CODEWITHSUNDEEP
mysqljoin
James Wilson
James Wilson

Reputation: 13

MYSQL - How can I get this left join to work?

SELECT 
    *
FROM
    food_types ft
        LEFT JOIN
    member_has_food_types mhft ON (ft.food_id = mhft.food_id)
 WHERE mhft.member_id = 3230

Okay, so here are the database tables

Food Types:

        FOOD_ID  | FOOD_TYPE | STATE
        1          APPLE       1
        2          ORANGE      1
        3          BANANA      1
        4          CAKE        1
        5          BACON       1

member_has_food_types:

        FOOD_ID  | MEMBER_ID 
        1          3230
        2          3230
        4          3230
        5          3230

So because member_has_food_types does not contain a number 3 food, then the report comes back like:

        FOOD_ID  | FOOD_TYPE | STATE | FOOD_ID  | MEMBER_ID 
        1          APPLE       1        1           3230    
        2          ORANGE      1        2           3230
        4          CAKE        1        4           3230
        5          BACON       1        5           3230

But I want it to include a NULL like this

        FOOD_ID  | FOOD_TYPE | STATE | FOOD_ID  | MEMBER_ID 
        1          APPLE       1        1           3230    
        2          ORANGE      1        2           3230
        3          BANANA      1        NULL        NULL
        4          CAKE        1        4           3230
        5          BACON       1        5           3230

My understanding is its not showing a NULL for those fields, because im joining on food_id. So is there any clever way I can get around this with a sub query??

Upvotes: 0

Views: 36

Answers (1)

isaace
isaace

Reputation: 3429

move the filter to the join

 SELECT * FROM food_types ft
 LEFT JOIN member_has_food_types mhft ON ft.food_id = mhft.food_id  
 AND mhft.member_id = 3230

Upvotes: 1

Related Questions