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