how to check for empty Left Join values where 3 tables are joined in Mysql

If you've found this post then you'll probably done quite a specific search for a problem.  I'll explain what I've solved here and you can see if it's relevant to what your trying to achieve.


I have 3 tables

Shifts
Projects
and User

What I need to do is run a check and see if I have any shifts that hasn't got user details attached to it.  And then return the details of this with some information from the Project table also

Here's the solution

SELECT tt.id, tt.project_id, tt.cdate, tt.start_time, tt.end_time, cp.user_id, cp.firstname, cp.lastname, pj.title, pj.location
FROM vfvi7_pf_time_tracking AS tt
LEFT JOIN vfvi7_comprofiler AS cp ON tt.user_id = cp.user_id
LEFT JOIN vfvi7_pf_projects AS pj ON tt.project_id = pj.id
WHERE cp.user_id IS NULL

the tricks here are this

* I've used LEFT JOIN twice

in previous mysql queries I've used the syntax

LEFT JOIN (
vfvi7_comprofiler AS cp, vfvi7_pf_projects AS pj
) ON ( tt.user_id = cp.user_id
AND tt.project_id = pj.id )

but this didn't work.

*  ALSO using IS NULL

'WHERE cp.user_id IS NULL'  - it's as simple as that; this will let me know the shifts that haven't got a user assigned.

Here's some phrases I used to search for this problem

how to check mysql for a column with no left join

mysql left join produces no results

mysql find rows that have no left join