Clever Geek Handbook

SQL: get all messages with any comments

I need to build some pretty basic SQL I suppose, but since this is a rare event that I work with DB these days, I can't figure out the details.

I have a "messages" table with the following columns:

id, signature, text

and a "comment" table with the following columns:

id, name, text, post_id

What would a (single) SQL statement look like that retrieves the captions of all posts that have one or more comments associated with it via the "post_id" key? A DBMS is MySQL if it has anything to do with a SQL query.

+1


source to share


7 replies 


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


+7


source 


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


I think using a join will be much faster than using an IN clause or subquery.

+2


source


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

No clause or count () needed.

edit: there must of course be an inner join (to avoid mistakes if the orphan comment is missing), thanks to jishi.

0


source 


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

Forget about counting and subqueries.

The inner join will select all comments with valid posts and exclude all posts with 0 comments. DISTINCT will combine duplicate header records for posts with more than 1 comment.

0


source


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 intention better than most others in this thread - "give me all posts ..." (select * from posts) "... who has comments" (where available (select * from comments ...)) ... This is essentially the same as the connections above, but since you are not actually making any connections, you don't have to worry about getting duplicate entries in posts, so you only get one entry per post.

0


source


I'm just getting away from my head, but maybe something like:

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


source


You are mainly looking at subquery execution -

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

This results in a subquery being executed SELECT COUNT(*)

for every row in the message table. Depending on the size of your tables, you can add an extra column comment_count

to the table posts

to keep the number of the corresponding one comments

, so you can simply do

SELECT p.caption FROM posts p WHERE comment_count > 1

-2


source






More articles:


All Articles