CODEWITHSUNDEEP
sqlmysql
Sally
Sally

Reputation: 1789

SQL returning aggregated results from one table as a column in a query

Say I have the following setup

---Posts---
|id
|Title
-----------

---Comments---
|id
|postid
|comment
-----------

Some Mock Data

Posts
ID        Title
1         Hello World
2         Good Bye
3         Pepsi

Comments
ID      postid      comment
 1        1         comment 1
 2        2         comment 2
 3        2         comment 3

I want to return back the Title from the Posts table and all the comments related to it via the id in the comments Table.

Something like.

Title           Comment
Hello World     comment1
Good Bye        comment2
                comment3
Pepsi           null

Is this possible just using SQL?

Upvotes: 0

Views: 61

Answers (4)

Naresh
Naresh

Reputation: 815

Select Title, 
       (SELECT GROUP_CONCAT(Comment) FROM Comments
         WHERE
         Comments.postid=posts.posts) as comments
FROM posts

Upvotes: 1

Incidently
Incidently

Reputation: 4349

Probably you are looking for this: GROUP_CONCAT

Upvotes: 0

Erwin Vrolijk
Erwin Vrolijk

Reputation: 363

SELECT posts.title, comments.comment FROM posts JOIN comments ON posts.id = comments.postid

Upvotes: 0

Michael Pakhantsov
Michael Pakhantsov

Reputation: 25370

 Select Title, Comment
 from Posts p LEFT Join Comments c on c.PostId = p.id
 Order by 1

However Title will be repeated, i.e. result will be:

 Title           Comment
 ------------------------
 Hello World     comment1
 Good Bye        comment2
 Good Bye        comment3
 Pepsi           null

Upvotes: 0

Related Questions