nadish nadish - 3 years ago 151
MySQL Question

Select query with join -syntax error

SELECT SampleSheet.Id,SampleSheet.Sample_Complete,SampleSheet.SampleName,"
+ "count(Job.sampleId) AS NumberOfSamples FROM (SampleSheet "
+ "LEFT JOIN Job ON SampleSheet.Id = Job.sampleId) "
+ "WHERE SampleSheet.Sample_Complete=?"
+ "GROUP BY SampleSheet.Id


Can anynone please tell me whats wrong in this query. I am getting syntax error.

It works Fine if I dont use the Where Condition. But I need to get the rows for the ones where the sample is complete.

SELECT SampleSheet.Id,SampleSheet.Sample_Complete,SampleSheet.SampleName,count(Job.sampleId) AS NumberOfSamples FROM (SampleSheet LEFT JOIN Job ON SampleSheet.Id = Job.sampleId) GROUP BY SampleSheet.Id

Answer Source

There are couple of suggestions. It seems to be space issue after WHERE clause

            + "WHERE SampleSheet.Sample_Complete=?"
            + "GROUP BY SampleSheet.Id

instead

            + "WHERE SampleSheet.Sample_Complete=? "
            + "GROUP BY SampleSheet.Id

You could also remove ( just after FROM and obviously ) as well. It is not required.

 FROM (SampleSheet "
            + "LEFT JOIN Job ON SampleSheet.Id = Job.sampleId) "
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download