CODEWITHSUNDEEP
mysqlsql
Raj
Raj

Reputation: 1437

Mysql query issue with left join

I have two tables and trying to join both of them based on primary and foreign key.But the problem is that in second table the foreign key has duplicate rows.

1 Table - category

catid   catname
1       AAA
2       BBB
3       CCC

2 Table - answers

ansid    catid
1        1
2        1
3        2
4        2

The result should be 

catid   catname   present in answers table
1       AAA       yes
2       BBB       yes
3       CCC       no

My query is

select * from category 
left join answers on category.catid=answers.catid
group by answers.catid

But it is not returning the results what I want.

Upvotes: 0

Views: 40

Answers (2)

Rahul
Rahul

Reputation: 77846

You can try like below

select c.*,
case when a.ansid is null then 'no' else 'yes' end as `present in answers table`
from category c
left join answers a on c.catid = a.catid

Upvotes: 1

juergen d
juergen d

Reputation: 204746

select c.catid, 
       c.catname, 
       case when sum(a.catid is not null) > 0
            then 'no' 
            else 'yes' 
       end as present_in_answers_table
from category c
left join answers a on c.catid = a.catid
group by c.catid, c.catname

Upvotes: 1

Related Questions