检索多个表中的多个列不存在或匹配行集（mysql php）

The original query that "works" is of the form:

SELECT a.name
     , a.empid
     , b.keydate
     , c.shiftid
     , c.shiftlabel
     , c.shifttime
     , c.shifttime2
     , d.storeid
     , d.storelabel
     , e.roleid
     , e.rolelabel
  FROM employee  a
  JOIN tblschedule  b
    ON b.empid = a.empid
  JOIN tblshift  c
    ON c.shiftid = b.shiftid
  JOIN tblstore  d
    ON d.storeid = b.storeid
  JOIN tblrole  e
    ON e.roleid = b.roleid
 WHERE d.storeid = :empstore
   AND b.keydate = :empdate

This should be equivalent to OP query. It's just reformatted to be more decipherable; replacing the old-school comma syntax for the join operations with the JOIN keyword, and relocating the join predicates to the ON clause.

I'm not understanding why OP can't just negate the predicates in the WHERE clause. That is, replace the WHERE clause in the query above with something like:

 WHERE ( d.storeid <> :empstore )
    OR ( b.keydate <> :empdate OR b.keydate IS NULL )

It seems to me that would return the set of rows OP wants to return.

But maybe there's something I'm not understanding.

In the more general case, to exclude from a set of rows returned by a query, rows that match rows returned by another query...

using an anti-join pattern is the normative approach. That's an outer join operation... return all rows from one query, along matching rows from another query, and then excluding the rows where a match was found.

In this particular case, the query would be of this form:

SELECT q1.*
  FROM ( 
         query1 
       ) q1 
  LEFT
  JOIN ( 
         query2
       ) q2
    ON q2.empid   = q1.empid
   AND q2.keydate = q1.keydate
   AND q2.shiftid = q1.shiftid 
 WHERE q2.empid IS NULL

This query says to return all rows from q1, along with matching rows from q2, but excluding rows where a match was found in q2. The trick is the WHERE clause that tests for a NULL value in q2.empid. The join predicate guarantees us that q2.empid will be non-NULL when a matching row is found in q2. So the only rows that will have a NULL value in q2.empid are rows from q1 that didn't have a matching row.

q2 would be the original query; the set of rows that OP does not want to return.

q1 would be the original query, omitting the WHERE clause. So, all of the rows... the rows OP wants to return along with the rows that OP wants to exclude.

Putting that all together, the query might look something like this:

SELECT q1.*
  FROM ( -- query1 - all rows including those we are going to exclude (omit WHERE clause)
         SELECT a.name
              , a.empid
              , b.keydate
              , c.shiftid
              , c.shiftlabel
              , c.shifttime
              , c.shifttime2
              , d.storeid
              , d.storelabel
              , e.roleid
              , e.rolelabel
           FROM employee  a
           JOIN tblschedule  b
             ON b.empid = a.empid
           JOIN tblshift  c
             ON c.shiftid = b.shiftid
           JOIN tblstore  d
             ON d.storeid = b.storeid
           JOIN tblrole  e
             ON e.roleid = b.roleid
       ) q1 
  LEFT
  JOIN (
         -- query2 - the rows that are going to be excluded
         SELECT a.name
              , a.empid
              , b.keydate
              , c.shiftid
              , c.shiftlabel
              , c.shifttime
              , c.shifttime2
              , d.storeid
              , d.storelabel
              , e.roleid
              , e.rolelabel
           FROM employee  a
           JOIN tblschedule  b
             ON b.empid = a.empid
           JOIN tblshift  c
             ON c.shiftid = b.shiftid
           JOIN tblstore  d
             ON d.storeid = b.storeid
           JOIN tblrole  e
             ON e.roleid = b.roleid
          WHERE d.storeid = :empstore
            AND b.keydate = :empdate
       ) q2
    ON q2.empid   = q1.empid
   AND q2.keydate = q1.keydate
   AND q2.shiftid = q1.shiftid 
 WHERE q2.empid IS NULL

But again, for this particular case, the anti-join pattern seems like a round-about way of simply negating the predicates in the WHERE clause.

thanks for the detailed explanations and writeup and sorry for replying late cos I was trying to digest the info and experimenting with your examples.

My final query is as below and working.

      $query = 'SELECT q1.* FROM (SELECT a.name , a.empid, b.keydate, c.shiftid, c.shiftlabel, c.shittime, c.shittime2, d.storeid, d.storelabel, e.roleid, e.rolelabel FROM tblschedule b JOIN employee a ON b.empid = a.empid JOIN tblshift c ON c.shiftid = b.shiftid JOIN tblstore d ON d.storeid = b.storeid JOIN tblrole e ON e.roleid = b.roleid) q1' 
  .' LEFT JOIN (SELECT a.name , a.empid, b.keydate, c.shiftid, c.shiftlabel, c.shittime, c.shittime2, d.storeid, d.storelabel, e.roleid, e.rolelabel FROM tblschedule b JOIN employee a ON b.empid = a.empid JOIN tblshift c ON c.shiftid = b.shiftid JOIN tblstore d ON d.storeid = b.storeid JOIN tblrole e ON e.roleid = b.roleid WHERE d.storeid ='.$empstore.' AND b.keydate ="'. $empstartdate.'") q2'
   .' ON q2.empid = q1.empid'
   .' AND q2.keydate = q1.keydate'
   .' AND q2.shiftid = q1.shiftid'
   .' WHERE q2.storeid IS NULL and q2.keydate is null and q2.empid is null';

by the way, this is the first question I have posted on this website and how do I go about marking your answer as the correct one.

thanks and have a nice day.