PDO不承认NOT EXISTS条款

Question

I am running a query via PDO. The query runs fine when I run it directly in phpMyAdmin 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 1

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.

Use parenthesis() to get a result from NOT EXISTS, because you want one or the other, then you use AND to get the created date range and finally an AND to join the results from company id.