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?

score 55 · Accepted Answer · edited Sep 02 '20 at 14:18

55

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.

edited Sep 02 '20 at 14:18

Sebastián Palma

32,692
6
40
59

answered Oct 23 '13 at 23:10

Ashalynd

12,363
2
34
37

1

your problem when you only selected one field from your subquery was that you could not do the join statement, because hours.location_id was not supplied by a subquery if you only selected type_id. – Ashalynd Oct 23 '13 at 23:13
That makes sense. And pretty obvious I suppose. Anyway this works. Thank you. – MAZUMA Oct 23 '13 at 23:15
Also check whether you really need to use (in the second case) LEFT JOIN instead of JOIN. If you don't want NULL type_id then I assume you might actually want to run a JOIN (which is usually quicker). – Ashalynd Oct 23 '13 at 23:16

score 0 · Answer 2 · answered Oct 23 '13 at 23:22

0

Since you want to have non-NULL values for type_id, you shouldn't be using a LEFT JOIN for it at all. You'll want 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 joins rows that actually exist. So you won't get any rows where location_hours doesn't have a corresponding location_id to locations.id. Then you just filter out NULL values for location_hours.type_id.

answered Oct 23 '13 at 23:22

Chill

1,093
6
13

If I run a join only it location rows. I want all location rows. I then want to see if hours have been set for those locations buy looking at whether or not there is a NULL value. – MAZUMA Oct 23 '13 at 23:25
I'm not sure if I understand what you mean. Could you rephrase it? As an aside, the reason why your LEFT JOIN wasn't working is because the subquery isn't selecting location_id but you are using it for the ON condition. – Chill Oct 23 '13 at 23:39
If I run a JOIN it excludes rows where no hours type_id exists. I want to get all location rows and then see if there is a value for the type_id for each location. I'm using that value to determine whether or not hours need to added or edited for that particular location. – MAZUMA Oct 24 '13 at 00:30
I'm thinking you may not understand how a JOIN works. Here's what the query I wrote does, in plain English. First it retrieves all the rows from the **locations** table. Then, for every *id* value it has retrieved, it looks at the **location_hours** table and gets all the rows that have a *location_id* equal to that *id*. So if there are 10 rows with *location_id* = 1, then the new result set will have 10 rows with *locations.id* = 1 and *location_hours.location_id* = 1. Then it filters checking that *type_id* is not null. – Chill Oct 24 '13 at 13:46

MySQL Left Join Subquery with *

2 Answers2