CODEWITHSUNDEEP
mysqlselect
fefe
fefe

Reputation: 9055

mysql different select queries in one

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

Upvotes: 3

Views: 95

Answers (1)

Pierre de LESPINAY
Pierre de LESPINAY

Reputation: 46158

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 ?

Upvotes: 3

Related Questions