mysql different select queries in one

Question

how can I have with one query the following: I would like to have from my comments table all the people how have been commenting on a given post_id and than check how many time the user has commented, based on his name. I would like to avoid to have 2 different queries for it

I have been trying the following but won't return to expected result

SELECT comments.*, COUNT(approved.comment_approved) AS has_commented  FROM wp_comments AS comments  
    INNER JOIN wp_comments AS approved 
    ON comments.comment_author = approved.comment_author

WHERE comments.comment_post_ID =14616
GROUP BY comments.comment_content

Can you tell the expected result vs the current one ? Even better build a sqlfiddle — Pierre de LESPINAY
– Pierre de LESPINAY, Commented Jun 3, 2013 at 8:31

Pierre de LESPINAY · Accepted Answer · 2013-06-03 09:33:04Z

3

Shouldn't you group by post_ID ? (that would return only one line)

SELECT
  comments.*
, COUNT(approved.comment_approved) AS "has_commented"
FROM wp_comments AS comments
JOIN wp_comments AS approved
  ON (comments.comment_author = approved.comment_author)
WHERE comments.comment_post_ID = 14616
GROUP BY comments.comment_post_ID
;

Or do you want one line per "approved" comment ?

edited Jun 3, 2013 at 9:33

answered Jun 3, 2013 at 8:30

Pierre de LESPINAY

46.4k62 gold badges222 silver badges318 bronze badges

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

Collectives™ on Stack Overflow

mysql different select queries in one

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