CODEWITHSUNDEEP
phpmysqlselectjoinunion
Mahdi Youseftabar
Mahdi Youseftabar

Reputation: 2352

select count of rows from 2 tables and merge into one row (mysqli)

i create a web app like facebook by php and mysqli

in my app i have a table for posts , one table for likes , and one table for comments

i want to get the number of comments and likes of each post in one row with his post_id!!!

i try some querys likes this :

select `tblpost`.`post_id`,  COALESCE(TCOMM.`comment_num`,0) as `c_num`, COALESCE(TLIKE.`like_num`,0) as `l_num`
from 
(select `tblpost`.`post_id`, count(*) as `like_num` from `tblpost` join `tbllikes` on `tbllikes`.`post_id` = `tblpost`.`post_id` group by `tblpost`.`post_id`
) TLIKE
inner join
(select `tblpost`.`post_id`, count(*) as `comment_num` from `tblpost` join `tblcomments` on `tblcomments`.`post_id` =  `tblpost`.`post_id` group by `tblpost`.`post_id`) TCOMM
on
    TCOMM.`post_id` = TLIKE.`post_id`

but i don't know what's my problem

Upvotes: 1

Views: 672

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

First, I think you can greatly simplify your query:

select l.post_id,
        COALESCE(c.comment_num, 0) as c_num, COALESCE(l.like_num, 0) as l_num
from  (select l.post_id, count(*) as like_num
       from tbllikes l 
       group by l.post_id
      ) l inner join
      (select c.post_id, count(*) as comment_num
       from tblcomments c
       group by c.post_id
      ) c
      on l.post_id = c.post_id;

This will only get you posts that have both likes and comments. To get what you want, use a left join:

select p.post_id,
      COALESCE(c.comment_num, 0) as c_num, COALESCE(l.like_num, 0) as l_num
from tblpost p left join
     (select l.post_id, count(*) as like_num
      from tbllikes l 
      group by l.post_id
     ) l
     on l.post_id = p.post_id left join
     (select c.post_id, count(*) as comment_num
      from tblcomments c
      group by c.post_id
     ) c
     on c.post_id = p.post_id;

Upvotes: 1

Haleemur Ali
Haleemur Ali

Reputation: 28253

You can do count distincts with two left joins.

Something like this would work if there are fields like_id and comment_id in the tables tbllikes and tblcomments

SELECT 
    tblpost.post_id AS post_id, 
    COUNT(DISTINCT tbllikes.like_id) AS likes,
    COUNT(DiSTINCT tblcomments.comment_id) AS comments
FROM tblpost 
LEFT JOIN tbllikes ON tbllikes.post_id = tblpost.post_id
LEFT JOIN tblcomments on tblcomments.post_id =  tblpost.post_id
GROUP BY tblpost.post_id

Upvotes: 2

Related Questions