Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

left join return null even if there are no rows

My tables:

Table cat has id, name

Table user has id, uname, catid

Sample data:
cat table

1 | Cate one
2 | cate two

user table

1 | sam | 1
2 | dam | 0

my query is

SELECT cat.id, cat.name 
FROM cat LEFT JOIN user 
  ON cat.id = user.catid
WHERE user.id = 2

Since there is no category with id 0 I get zero rows.
If there are no rows I want NULL or zeros as a result.

How do I do that?

like image 483
Jayapal Chandran Avatar asked Sep 14 '10 13:09

Jayapal Chandran


2 Answers

You either need to turn your left join into a right join or swap the tables around:

SELECT cat.id, cat.name
  FROM user LEFT JOIN cat ON cat.id = user.catid
 WHERE user.id = 2

With your example data, this will give you a row containing nulls as a result.

like image 54
Phil Ross Avatar answered Sep 19 '22 02:09

Phil Ross


Change your LEFT JOIN to a RIGHT JOIN... that should pull everything from the users table, and anything from the category table if it is available.

like image 20
LittleBobbyTables - Au Revoir Avatar answered Sep 20 '22 02:09

LittleBobbyTables - Au Revoir