CODEWITHSUNDEEP
mysqlsql
user1001176
user1001176

Reputation: 1165

mysql query not returning all results when using case

I am trying to get data from two tables . The first table is of sub category and the second is of sub category image . There is only one sub category which has image and the rest of them does not have a image . I made the following query so that the one which do not have a image can be with a default image 

select s.name,s.id,s.level,CASE WHEN g.photo_url IS NULL THEN 'no-image.png'  ELSE g.photo_url END AS photo_url  
from sub_category s,gallery g 
where g.main_id=s.id and g.type_id='9' and s.category_id='1' and s.level='1'

But it is returning one row ( the one which had image) and rest of the sub categories are not even shown .It was suppose to show 7 sub category one with the image and the rest of them with the default image for all of those who don't have a image in gallery table.

Upvotes: 0

Views: 57

Answers (1)

radar
radar

Reputation: 13425

you need to use left join

also use explicit join syntax.

select s.name,s.id,s.level,
CASE WHEN g.photo_url IS NULL THEN 'no-image.png'  
ELSE g.photo_url END 
AS photo_url  
from sub_category s
left join gallery g 
on g.main_id=s.id 
on g.type_id='9' 
where s.category_id='2' 
and s.level='1'

Upvotes: 1

Related Questions