Php Mysql Join two tables with multiple codition and null records

Question

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

And another table is ttreplycount where count stored as below:

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:

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.

How do you determine which row gets the votes value?

Gordon Linoff
– Gordon Linoff

2015-09-22 12:07:03 +00:00
Commented Sep 22, 2015 at 12:07 — Gordon Linoff
– Gordon Linoff, Commented Sep 22, 2015 at 12:07

Gordon Linoff · Accepted Answer · 2015-09-22 12:07:50Z

2

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 ;

answered Sep 22, 2015 at 12:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Php Mysql Join two tables with multiple codition and null records

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related