MySQL Left Join Subquery with *

Question

I'm putting together a fairly simple query with a subquery in the JOIN statement. It only works if I include an * in the subquery select. Why?

This 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';

This doesn't

$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 even be doing it this way? I thought * was not best if you don't need all the fields?

Answer 1

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

   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 you have to select in the inner query all the fields which are referenced in the outer query.