LEFT JOIN with condition in WHERE clause

Question

SELECT
    supplies.id,
    supplierId,
    supplies.date,
    supplies.commodity,
    supplier_payments.date AS paymentDate,
FROM
    supplies
INNER JOIN suppliers ON suppliers.id = supplies.supplierId
LEFT JOIN supplier_payments ON supplier_payments.supplyId = supplies.id
WHERE supplier_payments.isDeleted = 0 AND supplierId = 1

What I am trying is to get all records from supplies table and related records from supplier_payments but the supplier_payments.isDeleted should be equal to 0. What happens now that I only get records from supplies that have at least one supplier payment because of the condition. Is there a way to get all supply records and supply payments with condition?

Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, May 20 '20 at 21:59
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Stop trying to code your overall goal & explain what you expected instead from the given code & why. — philipxy, May 20 '20 at 22:00
This is a faq. Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. — philipxy, May 20 '20 at 22:00
I guess you are being sarcastic. Such rudeness is never appropriate. Your question is poor & a duplicate & my comments are relevant. — philipxy, May 20 '20 at 23:44
Nothing in my comments is negative & they are helpful & appropriate & your criticisms are not consistent with site conventions. I'm done. — philipxy, May 21 '20 at 00:05

GMB · Accepted Answer · 2020-05-20T21:00:45.453

Consider moving the condition on the LEFT JOINed table to the ON clause of the JOIN:

SELECT
    sr.id,
    se.supplierId,
    se.date,
    se.commodity,
    sp.date AS paymentDate,
FROM supplies se
INNER JOIN suppliers sr ON sr.id = se.supplierId
LEFT JOIN supplier_payments sp
    ON sp.supplyId = se.id
    AND sp.isDeleted = 0
WHERE se.supplierId = 1

Side notes:

in a multi-table query, always qualify each column with the table it belongs to, to make the query easier to follow and avoid ambiguity
table aliases make the query easier to read and write

LEFT JOIN with condition in WHERE clause

1 Answers1