AntG AntG - 5 months ago 11
SQL Question

PDO not recognising NOT EXISTS clause

I am running a query via PDO. The query runs fine when I run it directly in myPHPAdmin console, but returns incorrectly when run via PDO. It returns rows as though the NOT EXISTS clause were not there. My query is effectively like this:

SELECT ul.session, CONCAT(u.firstname,' ', u.lastname) AS owner, ...
FROM usagelog ul
INNER JOIN companies c ON ul.company=c.companyid
INNER JOIN users u ON ul.user=u.userid
WHERE (ul.company=:compid OR 0=:compid)
AND ul.created BETWEEN :start AND :end
AND NOT EXISTS(SELECT ss.session FROM seshstatechangelog ss WHERE ss.session=ul.session AND ss.stateto != :state)
ORDER BY ul.created


I have tried with both ? style binding of variables and : as you see here.

Has anyone else had this problem?

Answer
SELECT ul.session, CONCAT(u.firstname,' ', u.lastname) AS owner, ...
  FROM usagelog ul 
 INNER JOIN companies c ON ul.company=c.companyid
 INNER JOIN users u ON ul.user=u.userid
 WHERE (ul.company=:compid OR 0=:compid)
   AND (ul.created BETWEEN :start AND :end)
   AND NOT EXISTS(SELECT ss.session FROM seshstatechangelog ss WHERE ss.session=ul.session AND ss.stateto != :state) 
 ORDER BY ul.created

Give, AND (ul.created BETWEEN :start AND :end). When, there are many condition. Try to enclose it with using ( and ). It will be easy for readability. May be AND :end and AND NOT EXISTS is taken as one condition.

Comments