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.
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
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.
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.
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.
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.
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)
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