CODEWITHSUNDEEP
sqlmysql
Morten Christiansen
Morten Christiansen

Reputation: 19580

SQL: Get all posts with any comments

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.

Upvotes: 1

Views: 2739

Answers (7)

Tjofras
Tjofras

Reputation: 2096

SELECT caption FROM posts 
INNER JOIN comments ON comments.post_id = posts.id 
GROUP BY posts.id;

No need for a having clause or count().

edit: Should be a inner join of course (to avoid nulls if a comment is orphaned), thanks to jishi.

Upvotes: 0

Ian Varley
Ian Varley

Reputation: 9457

I find this syntax to be the most readable in this situation:

SELECT * FROM posts P 
  WHERE EXISTS (SELECT * FROM Comments WHERE post_id = P.id)

It expresses your intent better than most of the others in this thread - "give me all the posts ..." (select * from posts) "... that have any comments" (where exist (select * from comments ... )). It's essentially the same as the joins above, but because you're not actually doing a join, you don't have to worry about getting duplicates of the records in Posts, so you'll just get one record per post.

Upvotes: 0

James
James

Reputation: 3892

SELECT DISTINCT caption
FROM posts
    INNER JOIN comments ON posts.id = comments.post_id

Forget about counts and subqueries.

The inner join will pick up all the comments that have valid posts and exclude all the posts that have 0 comments. The DISTINCT will coalesce the duplicate caption entries for posts that have more then 1 comment.

Upvotes: 0

Nick DeVore
Nick DeVore

Reputation: 10166

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

Upvotes: 7

FlySwat
FlySwat

Reputation: 175653

SELECT DISTINCT p.caption, p.id
    FROM posts p, 
         comments c 
    WHERE c.post_ID = p.ID

I think using a join would be a lot faster than using the IN clause or a subquery.

Upvotes: 3

lomaxx
lomaxx

Reputation: 115833

Just going off the top of my head here but maybe something like:

SELECT caption FROM posts WHERE id IN (SELECT post_id FROM comments HAVING count(*) > 0)

Upvotes: -1

hark
hark

Reputation: 2438

You're basically looking at performing a subquery --

SELECT p.caption FROM posts p WHERE (SELECT COUNT(*) FROM comments c WHERE c.post_id=p.id) > 1;

This has the effect of running the SELECT COUNT(*) subquery for each row in the posts table. Depending on the size of your tables, you might consider adding an additional column, comment_count, into your posts table to store the number of corresponding comments, such that you can simply do

SELECT p.caption FROM posts p WHERE comment_count > 1

Upvotes: -2

Related Questions