retrieve multiple columns from multiple tables not exists or match on rows set (mysql php)

Question

I would like to seek the professional advice of this forum on a mysql query which have I have spend hours trying to get it right but to no avail.

So it goes like this. Query1 below which I will retrieve the name, employer id, date, shift id, shift label, shift time, store id, store name, role id and role label from 5 tables, tblshift, tblstore, tblrole, tblschedule and employee.

The tables tblshift, tblstore, tblrole and employer are linked to the tblschedule through their respective ids in their tables. The php variables empstore and empdate are values that are posted from the form.

$query1 = '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 as a, tblschedule as b, tblshift as c, tblstore as d, tblrole as e where a.empid=b.empid and b.shiftid=c.shiftid and b.storeid=d.storeid and b.roleid=e.roleid and d.storeid='.$empstore.' and b.keydate ="' . $empdate . '"';

The above query works correctly but I would also like to retrieve the opposite of this query which are the rows not present in the query. I have tried to use the 'NOT EXIST' and also 'NOT IN' statements in my query but either the query could not run or the rows are not correct. Please see below queries using 'NOT IN' and 'NOT EXISTS'statements. From query 2 and 3, you will observe that I have used empid, keydate and shiftid because these are primary keys in my tblschedule table and the other columns are based on this uniqueness.

*$query2 = 'select a1.name, a1.empid, b1.keydate, c1.shiftid, c1.shiftlabel, c1.shifttime, c1.shifttime2, d1.storeid, d1.storelabel, e1.roleid, e1.rolelabel from employee as a1, tblschedule as b1, tblshift as c1, tblstore as d1, tblrole as e1 where a1.empid=b1.empid and b1.shiftid=c1.shiftid and b1.storeid=d1.storeid and b1.roleid=e1.roleid'
 . ' where (a1.empid, b1.keydate, c1.shiftid) not in (select a2.empid, b2.keydate, c2.shiftid from employee as a2, tblschedule as b2, tblshift as c2, tblstore as d2, tblrole as e2 where a2.empid=b2.empid and b2.shiftid=c2.shiftid and b2.storeid=d2.storeid and b2.roleid=e2.roleid and d2.storeid='.$empstore.' and b2.keydate ="' . $empdate . '")';


$query3 = 'select a1.name, a1.empid, b1.keydate, c1.shiftid, c1.shiftlabel, c1.shifttime, c1.shifttime2, d1.storeid, d1.storelabel, e1.roleid, e1.rolelabel from employee as a1, tblschedule as b1, tblshift as c1, tblstore as d1, tblrole as e1 where a1.empid=b1.empid and b1.shiftid=c1.shiftid and b1.storeid=d1.storeid and b1.roleid=e1.roleid'
 . ' where not exists (select 1 from employee as a2, tblschedule as b2, tblshift as c2, tblstore as d2, tblrole as e2 where a2.empid=b2.empid and b2.shiftid=c2.shiftid and b2.storeid=d2.storeid and b2.roleid=e2.roleid and d2.storeid='.$empstore.' and b2.keydate ="' . $empdate . '" and a1.empid=a2.empid and b1.keydate=b2.keydate and c1.shiftid=c2.shiftid)';*

The complicated part about the queries is that I am trying to collect datas from multiple columns of other tables and I am also filtering the datas based on the unique columns empid, keydate and shiftid. I have found some sources from this website below but could not get it working.

How to retrieve non-matching results in mysql

get the opposite results from a SELECT query

mysql "Where not in" using two columns

Thanks in advance and look forward to learning from all of you here.

regards, dennis

Look into joins. Left and Inner joins – SyntaxLAMP Jan 03 '16 at 05:17 — SyntaxLAMP, Jan 03 '16 at 05:17

score 0 · Accepted Answer · answered Jan 03 '16 at 06:17

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.

If `keydate` can be NULL, and we want to "match" rows that have a NULL value for `keydate`, we can make use of the MySQL null-safe comparator.... `<=>` (the spaceship symbol). The join predicates guarantee us that `empid` and `shiftid` will not be NULL.). If this isn't the set of rows that OP is looking for, then we need some more clarification on the specification... what does "opposite rows" actually mean. — spencer7593, Jan 03 '16 at 06:25

score 0 · Answer 2 · answered Jan 05 '16 at 07:12

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.

retrieve multiple columns from multiple tables not exists or match on rows set (mysql php)

2 Answers2