Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Single SQL query on many to many relationship

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?

like image 381
yojimbo87 Avatar asked Jan 12 '10 20:01

yojimbo87


People also ask

How do you write a SQL query for many-to-many relationships?

When you need to establish a many-to-many relationship between two or more tables, the simplest way is to use a Junction Table. A Junction table in a database, also referred to as a Bridge table or Associative Table, bridges the tables together by referencing the primary keys of each data table.

Can you have a many-to-many relationship in SQL?

Many-to-Many relationship lets you relate each row in one table to many rows in another table and vice versa. As an example, an employee in the Employee table can have many skills from the EmployeeSkill table and also, one skill can be associated with one or more employees.

How do you handle a many-to-many relationship?

When you have a many-to-many relationship between dimension-type tables, we provide the following guidance: Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column. Add a bridging table to store associated entities. Create one-to-many relationships between the three tables.


2 Answers

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
like image 84
mitenka Avatar answered Sep 26 '22 08:09

mitenka


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
like image 33
JMM Avatar answered Sep 26 '22 08:09

JMM