CODEWITHSUNDEEP
mysql
Lifewithsun
Lifewithsun

Reputation: 998

Php Mysql Join two tables with multiple codition and null records

I have tried to fetch records from 2 tables but it is not showing required output where my first table is ttreply

ttreply table

And another table is ttreplycount where count stored as below:

ttreplycount

I have tried to fetch value but it doesn't fetch records as i want my mysql query as below:

SELECT r.id, r.userid, r.`reply`, coalesce(x.votes,0) as Votes 
FROM `ttreply` r left join 
(SELECT sum(votes) as Votes from `ttreplyvotes` )x ON r.`post_id`=2

Which showing me output like this:

Output

What i needed is like this:

id  userid    reply                Votes
2   3       Testing 2nd reply      -2
3   3       Testing 3nd reply       0
4   3        rewsf                  0

Any help would be helpful.

Upvotes: 3

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can get the rows you want by moving the condition to the where clause:

SELECT r.id, r.userid, r.`reply`, coalesce(x.votes,0) as Votes 
FROM `ttreply` r cross join 
     (SELECT sum(votes) as Votes from `ttreplyvotes` ) x 
WHERE r.`post_id` = 2 ;

This will have -2 for all three rows. Why do you want the total on only one row? How do you determine which row gets the total?

EDIT:

Oh, I think I understand:

SELECT r.id, r.userid, r.`reply`, coalesce(x.votes, 0) as Votes 
FROM `ttreply` r left join 
     (SELECT replyid, sum(votes) as Votes
      FROM `ttreplyvotes`
      GROUP BY replyid
     ) x 
     ON x.replyid = r.id
WHERE r.`post_id` = 2 ;

Upvotes: 2

Related Questions