SELECT bill.bill_id, bill.bill_ref
FROM bill
LEFT JOIN bill_item ON (bill.bill_id = bill_item.bill_id)
WHERE bill_item.job_sales_id = :job_sales_id and
bill.billing_type = "INV"
Я хочу добавить ниже в моем состоянии:
IF bill.bill_ref = "",
bill.bill_ref NOT IN
(SELECT cn_inv FROM bill a WHERE a.billing_type = "CN")
Как я должен кодировать это?
bill table:
bill_id | bill_ref | billing_type | cn_inv
--------+----------+--------------+-------
1 | INV001 | INV |
2 | | INV |
3 | INV002 | INV |
4 | CN001 | CN | INV002
------------------------------------------
bill_item table:
bill_id | item_code | sales_id
--------+-----------+----------
1 | item001 | 10
1 | item002 | 11
2 | item001 | 13
3 | item001 | 13
4 | item001 | 13
когда дано:
sales_id равен 13, возвращаемое rowCount равно 0.
sales_id равен 10, возвращаемое rowCount равно 1.
sales_id равен 11, возвращаемое rowCount равно 0.
Не совсем ясно, какой результат должен быть возвращен. Если спецификация исключать строки, где есть соответствующая строка в bill
мы можем использовать NOT EXISTS
с коррелированным подзапросом.
Если мы хотим, чтобы исключение применялось только тогда, когда bill_ref
не NULL и не пустая строка …
SELECT b.bill_id
, b.bill_ref
FROM bill b
JOIN bill_item i
ON i.bill_id = b.bill_id
AND i.job_sales_id = :job_sales_id
WHERE b.billing_type = 'INV'
AND b.bill_ref IS NOT NULL
AND NOT EXISTS ( SELECT 1
FROM bill a
WHERE a.cn_inv = b.bill_ref
AND a.cn_inv <> ''
AND a.billing_type = 'CN'
)
Обратите внимание, что это исключает строки, имеющие значение NULL для bill_ref
, Запрос можно настроить так, чтобы возвращались строки со значением NULL в bill_ref.
Мы могли бы также использовать шаблон анти-объединения, чтобы вернуть эквивалентный результат …
SELECT b.bill_id
, b.bill_ref
FROM bill b
JOIN bill_item i
ON i.bill_id = b.bill_id
AND i.job_sales_id = :job_sales_id
-- anti-join exclude rows that have a match
LEFT
JOIN bill a
ON a.billing_type = 'CN'
AND a.cn_inv <> ''
AND a.cn_inv = b.bill_ref
WHERE a.cn_inv IS NULL
AND b.billing_type = 'INV'
AND b.bill_ref IS NOT NULL
В запросе OP есть условие в предложении WHERE, требующее job_sales_id
от bill_item
таблица должна быть ненулевой. Это условие сводит на нет «внешность» ЛЕВОГО соединения. Так что это эквивалентно ВНУТРЕННЕМУ объединению.
Запросы в этом ответе сохраняют это поведение. (С LEFT
ключевое слово удалено для объединения с bill_item
мы можем переместить условие из предложения WHERE в предложение ON.)
Других решений пока нет …