I have two tables talk_comments and talk_comment_votes.

I run the following code to select, commentId, numberOfUpvotes, whetherUserUpvoted, numberOfDownvotes, whetherUserDownvoted usin LEFT JOINs to the same table.

SELECT c.id, COUNT(v1.id) as upvotes, COUNT(v2.id) as userUpvoted, COUNT(v3.id) as downvotes, COUNT(v4.id) as userDownvoted FROM talk_comments c

    LEFT JOIN talk_comment_votes v1 ON v1.comment_id = c.id AND v1.status = 1 

    LEFT JOIN talk_comment_votes v2 ON v2.comment_id = c.id AND v2.status = 1 AND v2.user_id = 1 AND v2.is_anonymous = 0

    LEFT JOIN talk_comment_votes v3 ON c.id = v3.comment_id AND v3.status = 2

    LEFT JOIN talk_comment_votes v4 ON c.id = v4.comment_id AND v4.status = 2 AND v4.user_id = 1 AND v4.is_anonymous = 0

WHERE c.id = 2 GROUP BY c.id

I have the following data in my talk_comment_votes table

So, according to the query, it should select values 2,2,0,1,1 respectively. When I break those JOIN statements and do the queries, it returns the expected results. But, with JOINs, it returns something like the follows.

Can I get some help on fixing this?

Thanks.

I ran a benchmark on queries based on @spencer7593 and @RaymondNijland's 2 answers.

LEFT JOINS wins!

1. Using LEFT JOINS

SELECT c.id, COUNT(DISTINCT v1.id) as upvotes, COUNT(DISTINCT v2.id) as userUpvoted, COUNT(DISTINCT v3.id) as downvotes, COUNT(DISTINCT v4.id) as userDownvoted FROM talk_comments c

  LEFT JOIN talk_comment_votes v1 ON v1.comment_id = c.id AND v1.status = 1 

    LEFT JOIN talk_comment_votes v2 ON v2.comment_id = c.id AND v2.status = 1 AND v2.user_id = 1 AND v2.is_anonymous = 0

    LEFT JOIN talk_comment_votes v3 ON c.id = v3.comment_id AND v3.status = 2

    LEFT JOIN talk_comment_votes v4 ON c.id = v4.comment_id AND v4.status = 2 AND v4.user_id = 1 AND v4.is_anonymous = 0

WHERE c.id = 2 GROUP BY c.id

Time for 1000 queries: 0.55000805854797s

2. Using Sub Queries

SELECT c.id,c.user_id, c.time,c.body, c.reply_to, 

    (SELECT COUNT(v1.id) FROM talk_comment_votes v1 WHERE v1.comment_id = c.id AND v1.status = 1 LIMIT 1) as upvotes, 

    (SELECT COUNT(v2.id) FROM talk_comment_votes v2 WHERE v2.comment_id = c.id AND v2.status = 1 AND v2.user_id = 1 LIMIT 1) as clientUpvoted,

    (SELECT COUNT(v3.id) FROM talk_comment_votes v3 WHERE v3.comment_id = c.id AND v3.status = 2 LIMIT 1) as downvotes, 

    (SELECT COUNT(v4.id) FROM talk_comment_votes v4 WHERE v4.comment_id = c.id AND v4.status = 2 AND v4.user_id = 1 LIMIT 1) as clientDownvoted

      FROM talk_comments c 

           WHERE c.id = 2 GROUP BY c.id

Time for 1000 queries: 0.95499300956726s

3. Using SUM, IF

SELECT c.id

     , SUM(IF(v.status = 1                                          ,1,0)) AS upvotes

     , SUM(IF(v.status = 1 AND v.user_id = 1 AND v.is_anonymous = 0 ,1,0)) AS userUpvoted

     , SUM(IF(v.status = 2                                          ,1,0)) AS downvotes

     , SUM(IF(v.status = 2 AND v.user_id = 1 AND v.is_anonymous = 0 ,1,0)) AS userDownvoted

    FROM talk_comments c

    LEFT

    JOIN talk_comment_votes v

      ON v.comment_id = c.id

   WHERE c.id = 2

   GROUP BY c.id

Time for 1000 queries: 1.2266919612885s

Thank you for all the answers.

I'd use conditional aggregation. A join to a single reference to tall_comment_votes, and then check conditions in expressions.

SELECT c.id

     , SUM(IF(v.status = 1                                          ,1,0)) AS upvotes

     , SUM(IF(v.status = 1 AND v.user_id = 1 AND v.is_anonymous = 0 ,1,0)) AS userUpvoted

     , SUM(IF(v.status = 2                                          ,1,0)) AS downvotes

     , SUM(IF(v.status = 2 AND v.user_id = 1 AND v.is_anonymous = 0 ,1,0)) AS userDownvoted

  FROM talk_comments c

  LEFT

  JOIN talk_comment_votes v

    ON v.comment_id = c.id

 WHERE c.id = 2

 GROUP

    BY c.id

This avoids the problem of the partial cross product, when there are multiple rows returned from v1, v2, v3 and v4.

The MySQL IF() expression could replaced with a more ANSI standards compliant CASE expression, e.g.

    , SUM(CASE WHEN v.status = 1 THEN 1 ELSE 0 END)  AS upvotes

FOLLOWUP

setup test case and observe execution plans and performance

populate tables

CREATE TABLE talk_comments (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT);

CREATE TABLE talk_comment_votes (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, comment_id  INT UNSIGNED NOT NULL, user_id  INT UNSIGNED NOT NULL, is_anonymous TINYINT(1) UNSIGNED NOT NULL, STATUS TINYINT UNSIGNED, time_ INT UNSIGNED); 

CREATE INDEX talk_comment_votes_IX1 ON talk_comment_votes (comment_id, STATUS, user_id, is_anonymous) ;

INSERT INTO talk_comments (id) VALUES (1),(2),(3);

INSERT INTO talk_comment_votes (id, comment_id, user_id, is_anonymous, STATUS, time_) VALUES (1,2,2,0,1,0),(2,1,1,0,1,0),(3,2,1,0,2,NULL),(4,7,1,0,2,NULL),(5,1,14,1,1,NULL),(6,2,14,1,1,NULL);

query execution plans

EXPLAIN

SELECT c.id, COUNT(DISTINCT v1.id) AS upvotes, COUNT(DISTINCT v2.id) AS userUpvoted, COUNT(DISTINCT v3.id) AS downvotes, COUNT(DISTINCT v4.id) AS userDownvoted FROM talk_comments c

  LEFT JOIN talk_comment_votes v1 ON v1.comment_id = c.id AND v1.status = 1 

    LEFT JOIN talk_comment_votes v2 ON v2.comment_id = c.id AND v2.status = 1 AND v2.user_id = 1 AND v2.is_anonymous = 0

    LEFT JOIN talk_comment_votes v3 ON c.id = v3.comment_id AND v3.status = 2

    LEFT JOIN talk_comment_votes v4 ON c.id = v4.comment_id AND v4.status = 2 AND v4.user_id = 1 AND v4.is_anonymous = 0

WHERE c.id = 2 GROUP BY c.id

;



EXPLAIN

SELECT c.id

     , SUM(IF(v.status = 1                                          ,1,0)) AS upvotes

     , SUM(IF(v.status = 1 AND v.user_id = 1 AND v.is_anonymous = 0 ,1,0)) AS userUpvoted

     , SUM(IF(v.status = 2                                          ,1,0)) AS downvotes

     , SUM(IF(v.status = 2 AND v.user_id = 1 AND v.is_anonymous = 0 ,1,0)) AS userDownvoted

    FROM talk_comments c

    LEFT

    JOIN talk_comment_votes v

      ON v.comment_id = c.id

   WHERE c.id = 2

   GROUP BY c.id

;

output from explain

--     id  select_type  table   type    possible_keys           key                     key_len  ref                        rows  Extra        

-- ------  -----------  ------  ------  ----------------------  ----------------------  -------  -----------------------  ------  -------------

--      1  SIMPLE       c       const   PRIMARY                 PRIMARY                 4        const                         1  Using index  

--      1  SIMPLE       v1      ref     talk_comment_votes_IX1  talk_comment_votes_IX1  6        const,const                   2  Using index  

--      1  SIMPLE       v2      ref     talk_comment_votes_IX1  talk_comment_votes_IX1  11       const,const,const,const       1  Using index  

--      1  SIMPLE       v3      ref     talk_comment_votes_IX1  talk_comment_votes_IX1  6        const,const                   1  Using index  

--      1  SIMPLE       v4      ref     talk_comment_votes_IX1  talk_comment_votes_IX1  11       const,const,const,const       1  Using index  







--     id  select_type  table   type    possible_keys           key                     key_len  ref       rows  Extra        

-- ------  -----------  ------  ------  ----------------------  ----------------------  -------  ------  ------  -------------

--      1  SIMPLE       c       const   PRIMARY                 PRIMARY                 4        const        1  Using index  

--      1  SIMPLE       v       ref     talk_comment_votes_IX1  talk_comment_votes_IX1  4        const        3  Using index  

measured performance:

100 executions                        round 1      round 2     round 3

------------------------------------  ----------   ----------  ---------

multiple left join, count(distinct    0.123 secs   0.130 secs  0.125 secs

conditional aggregation sum(if        0.113 secs   0.114 secs  0.111 secs