Single SQL query on many to many relationship

Question

I have a simple database with few tables (and some sample columns):

Posts (ID, Title, Content)

Categories (ID, Title)

PostCategories (ID, ID_Post, ID_Category)

Is there a way to create single SQL query which will return posts with categories that are assigned to each post?

Answer 1

select p.*, c.*
from Posts p
inner join PostCategories pc on p.ID = pc.ID_Post
inner join Categories c on pc.ID_Category = c.ID

If you mean with only one record per post, I will need to know what database platform you are using.

Answer 2

Sure. If I understand your question correctly, it should be as simple as

SELECT Posts.title, Categories.title 
FROM Posts, Categories, PostCategories 
WHERE PostCategories.ID_Post = Posts.ID AND PostCategories.ID_Category = Categories.ID 
ORDER BY Posts.title, Categories.title;

Getting one row per Post will be a little more complicated, and will depend on what RDBMS you're using.

Answer 3

You can use the GROUP_CONCAT function

select p.*, group_concat(DISTINCT c.title ORDER BY c.title DESC SEPARATOR ', ')
from Posts p
inner join PostCategories pc on p.ID = pc.ID_Post
inner join Categories c on pc.ID_Category = c.ID
group by p.id, p.title, p.content