CODEWITHSUNDEEP
mysqlsql
Genadinik
Genadinik

Reputation: 18649

Selecting all items in one table and join with another table, allowing nulls

I have a requirement to select all values from the table birds (basically all birds), and then joining with another table which tracks who likes that bird.

So I want the query to return all birds, and ids of records where people like that bird. And if there is no record of anyone liking this bird, then that field should be null.

My current query isn't getting the nulls. Here it is:

select  bird_name, member_id 
from birds  
right join bird_likes on birds.bird_id = bird_likes.bird_id 
where member_id = 2 ;

What could I do to make sure each row in the birds table is getting displayed once?

Upvotes: 8

Views: 35928

Answers (4)

Joe Phillips
Joe Phillips

Reputation: 51200

SELECT bird_name, member_id
FROM birds
LEFT JOIN bird_likes ON birds.bird_id=bird_likes.bird_id AND member_id=2

Upvotes: 3

krtek
krtek

Reputation: 26617

you must use left join instead of right join

The different joins

inner join : keep only the rows where there's data in both table

left join : keep all the rows of the left table and add what is possible from the right one

right join : keep all the rows of the right table and add what is possible from the left one

The left table is always the table we already have and the right table is the one we are joining with.

For the record, there is also a cross join which joins each row in the left table with each row in the right table, but this one isn't used very often.

I hope all this is now clearer for you :)

Corrected query

select  bird_name, member_id 
from birds  
left join bird_likes on birds.bird_id = bird_likes.bird_id 
where member_id = 2;

Be aware that this assumes that the column member_id is in the bird table, otherwise you can keep the condition like this :

select  bird_name, member_id 
from birds  
left join bird_likes on 
    birds.bird_id = bird_likes.bird_id and
    bird_likes.member_id = 2;

Upvotes: 30

RDL
RDL

Reputation: 7961

You want to use left outer join in this case

select  bird_name, member_id 
from birds  
left outer join bird_likes on birds.bird_id = bird_likes.bird_id 
where member_id = 2;

This will return all bird names and 'null' for ones with empty likes.

Upvotes: 3

Chris Cherry
Chris Cherry

Reputation: 28574

Change your RIGHT JOIN to a LEFT JOIN, that will bring in all birds records whether or not they have a relationship to bird_likes

Upvotes: 1

Related Questions