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.
source to share
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.
source to share
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.
source to share
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
source to share