left join issue with mysql

Question

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 multiple duplicate rows.

Structure :-

1 Table - category

catid   catname
1       AAA
2       BBB
3       CCC

2 Table - answers

ansid    catid     userid
1        1         9
2        1         9
3        2         9
4        2         6

The result should be

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

My query is

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

But it is not returning the results what I want.It returns only one row that is

userid    catid   catname   present in answers table    
6          2         BBB       yes

Gordon Linoff · Accepted Answer · 2014-08-01 15:21:31Z

3

I think you need to switch the order of the joins, so you keep everything in the category table and then move the where condition to the on clause:

SELECT a.userid, c.catid, c.catname, 
       (case when count(a.catid) > 0 then 'yes' else 'no'
        end) as present_in_answers_table 
from category c left join
     answers a  
     on c.catid = a.catid and
        a.userid = 6
group by c.catid;

Note that I also changed the sum() to a count() -- count() automatically counts the number of times the argument is not NULL.

edited Aug 1, 2014 at 15:21

answered Aug 1, 2014 at 15:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raj Over a year ago

Thanks Gordon...Entirely forgot to keep that thing.Such a stupid I am.Thanks once again.Accepted.

Collectives™ on Stack Overflow

left join issue with mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related