...

View Full Version : Resolved subquery



shaileshpatil
03-30-2009, 06:46 AM
Hello everyone

I used this query to find the count in second table.

SELECT e.scid, e.executionid, e.service, e.clientid, e.clientname, e.invoiceno, e.execstatus, e.sysdate, e.cuname,
e.upuname, e.canreason, e.udate, e.enquiryno,
(
select count(dm.gid) from datamodification dm where dm.status=0 and dm.scid=e.scid and dm.department='Fumigation' and dm.scid=e.scid
) AS cnt
FROM execution e
WHERE e.scid = 'BR001'
AND e.service = 'Container'
AND e.clientname LIKE '%%'
AND e.executionid LIKE '%%'
ORDER BY sysdate, executionid
limit 0,30


I get an error when i run this query.
MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

When i remove the where condition from subquery it works fine, but this is not what i want.

Kindly verify my sql syntax and suggest me what should be my query.

Thanks,

shaileshpatil
03-30-2009, 09:39 AM
I changed the sequence of the query.





SELECT (
select count(dm.gid) from datamodification dm where dm.status=0 and dm.scid=e.scid and dm.department='Fumigation' and dm.scid=e.scid
) AS cnt ,
e.scid, e.executionid, e.service, e.clientid, e.clientname, e.invoiceno, e.execstatus, e.sysdate, e.cuname,
e.upuname, e.canreason, e.udate, e.enquiryno
FROM execution e
WHERE e.scid = 'BR001'
AND e.service = 'Container'
AND e.clientname LIKE '%%'
AND e.executionid LIKE '%%'
ORDER BY sysdate, executionid
limit 0,30

I took the subquery first and then normal select.
It worked.
Thanks for your time



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum