CODEWITHSUNDEEP
mysql
user3608238
user3608238

Reputation: 425

MySQL LEFT JOIN filtering rows where NULL

I am attempting to return whether a user has favourited an item or not. I have the MySQL statement:

SELECT *,`user_choice`.fav FROM `items`
LEFT JOIN `user_choice` ON `user_choice`.item_id = `items`.item_id

This returns a full set of results, with user_choice.fav equal to 0, 1 or NULL

However, I would like to only show the user_choice.fav (whatever that value may be) for a given user, so I have added a WHERE statement for that user like so:

SELECT *,`user_choice`.fav FROM `items`
LEFT JOIN `user_choice` ON `user_choice`.item_id = `items`.item_id
WHERE `user_choice`.user_id=xx

When I add this WHERE statement, it filters out any results from the first statement where user_choice.fav is NULL (ie not 1 or 0). How can I get it to return values even when they are NULL

example data:

items

item_id | item_name

1,itemname

2,item2name

3,item3name

user_choice

user_id | item_id | fav

1,1,1

1,3,0

Upvotes: 1

Views: 6090

Answers (1)

Luke Peterson
Luke Peterson

Reputation: 8871

0, 1 are not considered NULL values, so your WHERE statement is excluding them. You need to add another OR into your WHERE clause as follows:

SELECT *,`user_choice`.fav FROM `items`
LEFT JOIN `user_choice` ON `user_choice`.item_id = `items`.item_id
WHERE `user_choice`.user_id=xx OR `user_choice`.user_id IS NULL

Upvotes: 4

Related Questions