Single SQL query on many to many relationship

Simple joins work well.

SELECT posts.id, posts.title, categories.id, categories.title
FROM posts
JOIN posts_categories ON posts.id = posts_categories.post_id
JOIN categories ON posts_categories.category_id = categories.id

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.


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