-2

I have the following querie:

SELECT *
FROM work
LEFT JOIN users ON work.user_id = users.user_id
LEFT JOIN customer ON work.customer_id = customer.customer_id
WHERE customer.visible = 0

the problem is about the "LEFT JOIN customer ON..." and the WHERE condition "customer.visible = 0 ". I have entries in my table "work" where "customer_id = NULL" and they get removed from the select because there obviously is no customer with ID "NULL" in the customer table.

I would like to keep all entries where "customer_id = NULL" in my work table.

webGuy
  • 143
  • 1
  • 3
  • 13
  • Move this condition: `customer.visible = 0` to the ON clause and remove WHERE. – forpas Jan 19 '20 at 14:37
  • 1
    Explanation: When there is no matching `customer` row, an empty `customer` row gets joined. All `customer` columns in that row are null. Its `visible` column is null, hence, not 0. – Thorsten Kettner Jan 19 '20 at 14:49
  • 1
    Problem is when you join and customer id is null, all of customer related columns become NULL in that row. That makes customer.visibility = NULL and your statement doesn't work. – Victor Jan 19 '20 at 14:49
  • Never use `SELECT *`. Always name, and properly qualify, the columns you actually want returned – Strawberry Jan 19 '20 at 15:23
  • Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After an OUTER JOIN ON, a WHERE or INNER JOIN that requires a right [sic] table column to be not NULL removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. – philipxy Jan 19 '20 at 21:13
  • This is a faq. Before considering posting please always 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. See [ask] & the voting arrow mouseover texts. Please in code questions give a [mre]. PS If either argument of SQL = is NULL, it returns NULL, and WHERE keeps rows with a true condition. PS You don't clearly explain exactly how your result is a function of inputs. – philipxy Jan 19 '20 at 21:15

1 Answers1

0

The trickiest part here is, using where condition in the LEFT join makes it Inner join. You probably need -

SELECT *
FROM work
LEFT JOIN users ON work.user_id = users.user_id
LEFT JOIN customer ON work.customer_id = customer.customer_id
                   AND customer.visible = 0
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40