I am not an SQL expert and have difficulties building a complicated query.
I use the tool flyspeed query to design the query visually.
In that tool the query works and returns records from mysql when I run the same query on MySQL I got zero records.
This is the query
Select * From
florence.room Inner Join
florence.rel_flo_roo On florence.rel_flo_roo.ROO_ID = florence.room.ROO_ID
florence.floor On florence.rel_flo_roo.FLO_ID = florence.floor.FLO_ID
On florence.room.ROO_ID = florence.fl_opname_defect.ROO_ID Inner Join
florence.fl_uptake On florence.fl_opname_defect.Completed =
florence.fl_opname_defect.Completed = 0
In the attachment you see the query I have made so far. I want to get all the rooms on a floor and see if ther are defects/maintenance items for that floor. So all the rooms on a floor and some of them has maintenance items.
you use LEFT JOIN to attach fl_opname_defect rows to the intermediate rows being built by the FROM clause
this allows for the possibility that some rooms don't have defects, so the columns in the intermediate rows that would come from fl_opname_defect will be set to NULL
(that's not news, that's the way left joins work)
but then you INNER JOIN fl_uptake rows based on a match between fl_uptake and fl_opname_defect
that means any intermediate rows where fl_opname_defect.Completed is NULL because fl_opname_defect was missing, will be discarded, because NULL is not equal to any value that fl_uptake.UPT_ID might have