SQL Left Join a Table on a Left Joined Table

Iam currently trying to left join a table on a left joined table as follows. I have the tables:

• accounts (id, vorname, nachname)
• projektkurse (id, accounts_id, projektwochen_id)
• projektkurs_einzel (id, projektkurse_id)
• projektkurs_einzel_zeiten (id, date, shift, projektkurs_einzel_id)

Now I want to get every account and the amount times they have an entry inside of projektkurs_einzel_zeiten, which should also be unique. So having the same date and shift multiple times does not count as multiple entries. The result should also be limited by the column projektwochen_id from the table projektkurse. This column should match a certain value for example 8.

Some Accounts don’t have any entries in projektkurse, projektkurs_einzel and projektkurs_einzel_zeiten, this is why my first thought was using LEFT JOIN like this:

SELECT accounts.id, accounts.vorname, accounts.nachname, COUNT(DISTINCT projektkurs_einzel_zeiten.date, projektkurs_einzel_zeiten.shift) AS T
FROM accounts
LEFT JOIN projektkurse on accounts.id = projektkurse.creator_id
LEFT JOIN projektkurs_einzel on projektkurse.id = projektkurs_einzel.projektkurs_id
LEFT JOIN projektkurs_einzel_zeiten ON projektkurs_einzel.id = projektkurs_einzel_zeiten.projektkurs_einzel_id
WHERE projektkurse.projektwochen_id = 8
GROUP BY accounts.id

This query does not achieve exactly what I want. It only returns accounts that have atleast one entry in projektkurse even if they have none in projektkurs_einzel and projektkurs_einzel_zeiten. The Count is obviously 0 for them but the accounts that have no entries in projektkurse are being ignored completly. How can I also show the accounts that don’t have entries in any other table with the Count 0 aswell?

Answer

You should not use eft join table’s column ins where condition this work as inner join

You should move the where condition for a left joined table in the corresponding ON clause

SELECT accounts.id, accounts.vorname, accounts.nachname, COUNT(DISTINCT projektkurs_einzel_zeiten.date, projektkurs_einzel_zeiten.shift) AS T
FROM accounts
LEFT JOIN projektkurse on accounts.id = projektkurse.creator_id 
                          AND projektkurse.projektwochen_id = 8
LEFT JOIN projektkurs_einzel on projektkurse.id = projektkurs_einzel.projektkurs_id
LEFT JOIN projektkurs_einzel_zeiten ON projektkurs_einzel.id = projektkurs_einzel_zeiten.projektkurs_einzel_id
GROUP BY accounts.id