Ad

SQL: Get All Posts With Any Comments

- 1 answer

I need to construct some rather simple SQL, I suppose, but as it's a rare event that I work with DBs these days I can't figure out the details.

I have a table 'posts' with the following columns:

id, caption, text

and a table 'comments' with the following columns:

id, name, text, post_id

What would the (single) SQL statement look like which retrieves the captions of all posts which have one or more comments associated with it through the 'post_id' key? The DBMS is MySQL if it has any relevance for the SQL query.

Ad

Answer

select p.caption, count(c.id)
from posts p join comments c on p.id = c.post_id
group by p.caption
having count (c.id) > 0
Ad
source: stackoverflow.com
Ad