Q: SQL left join select first table once Ive currently have the following problem:
I've got a MySQL Query:
SELECT account.id as id, `cname`, `username`, `state`, `dow` 
FROM account 
LEFT JOIN zeitplanung 
ON account.id = zeitplanung.user_id 
WHERE account.hide = 0 
and ifnull(zeitplanung.kw,43) = 43 
and ifnull(zeitplanung.year, 2017) = 2017;

It returns the usernames and mentioned values for them, in case i've selected the right year and kw, and if there are no entries in the joined table, it returns the values from the table account, and all other values as NULL.
But it doesnt return usernames, in case there are entries in the table "zeitplanung" which doesn't meet the cafeterias (kw, year).
How do I have to write that query to:


*

*get all entries from second table in case matching entries exist

*get the username and second table entries as NULL in case the WHERE statement won't fit.


A: The problem is that you are filtering out the NULL results in your WHERE clause.  To keep them, move any filtering conditions for the OUTER tables to their ON clause:
SELECT account.id as id, `cname`, `username`, `state`, `dow` 
FROM account 
LEFT JOIN zeitplanung 
ON account.id = zeitplanung.user_id 
AND zeitplanung.kw = 43
AND zeitplanung.year = 2017
WHERE account.hide = 0;

