MySQL Left Join Subquery with *

Question

I am compiling a fairly simple query with a subquery in a JOIN statement. It only works if I include * in the subquery. Why?

It works

$sql = 'SELECT locations.id, title, name, hours.lobby FROM locations LEFT JOIN states ON states.id = locations.state_id LEFT JOIN (SELECT *, type_id IS NOT NULL AS lobby FROM location_hours) AS hours ON locations.id = hours.location_id GROUP BY locations.id'; 

Is not

 $sql = 'SELECT locations.id, title, name, hours.lobby FROM locations LEFT JOIN states ON states.id = locations.state_id LEFT JOIN (SELECT type_id IS NOT NULL AS lobby FROM location_hours) AS hours ON locations.id = hours.location_id GROUP BY locations.id'; 

Should I do it like this? I thought no better if you don't need all the fields?

Answer 1

try this (if I understood your intent correctly, that you want to filter by type_id not null):

 $sql = 'SELECT locations.id, title, name, hours.lobby FROM locations LEFT JOIN states ON states.id = locations.state_id LEFT JOIN (SELECT location_id, type_id AS lobby FROM location_hours WHERE type_id IS NOT NULL) AS hours ON locations.id = hours.location_id GROUP BY locations.id'; 

The explanation is that in the internal query it is necessary to select all the fields referenced by the external queries.

Answer 2

Since you want to have non-NULL values ​​for type_id, you should not use LEFT JOIN for it at all. You will need the following, which uses a standard JOIN.

 $sql = 'SELECT locations.id, title, name, location_hours.type_id FROM locations JOIN location_hours ON location_hours.location_id = locations.id LEFT JOIN states ON states.id = locations.state_id WHERE location_hours.type_id IS NOT NULL GROUP BY locations.id'; 

The whole point of a JOIN is that it only connects to existing strings. This way you will not get any lines where location_hours does not have the corresponding location_id for location.id. Then you just filter the NULL values ​​for location_hours.type_id.