извлекать несколько столбцов из нескольких таблиц, которых не существует, или совпадать с набором строк (MySQL PHP)

Исходный запрос, который «работает», имеет форму:

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

Это должно быть эквивалентно OP-запросу. Это просто переформатировано, чтобы быть более расшифрованным; заменяя синтаксис старой школы запятыми для операций соединения JOIN ключевое слово и перемещение предикатов объединения в ON пункт.

Я не понимаю, почему ОП не может просто отрицать предикаты в WHERE пункт. То есть заменить WHERE предложение в запросе выше с чем-то вроде:

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

Мне кажется, что бы вернуть множество строк, которые OP хочет вернуть.

Но, может быть, я чего-то не понимаю.

В более общем случае, чтобы исключить из набора строк, возвращаемых запросом, строки, соответствующие строкам, возвращенным другим запросом …

используя анти-присоединиться шаблон является нормативным подходом. Это внешняя операция соединения … возвращает все строки из одного запроса, соответствующие строки из другого запроса, а затем исключая строки, в которых найдено совпадение.

В этом конкретном случае запрос будет иметь такую ​​форму:

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

Этот запрос говорит, что нужно вернуть все строки из q1 вместе с совпадающими строками из q2, но исключая строки, в которых совпадение было найдено в q2. Уловка — это предложение WHERE, которое проверяет значение NULL в q2.empid. Предикат соединения гарантирует нам, что q2.empid будет отличным от NULL, когда в q2 найдена подходящая строка. Таким образом, единственные строки, которые будут иметь значение NULL в q2.empid, — это строки из q1, у которых не было соответствующей строки.

q2 будет исходный запрос; набор строк, которые делает OP не хочу вернуться.

q1 будет оригинальный запрос, опуская WHERE пункт. Итак, все строки … строки, которые OP хочет вернуть вместе со строками, которые OP хочет исключить.

Собрав все это вместе, запрос может выглядеть примерно так:

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

Но опять же, в данном конкретном случае паттерн против объединения выглядит как обходной способ простого отрицания предикатов в предложении WHERE.