AlbaStar AlbaStar - 6 months ago 8
SQL Question

How do I combine data from 3 tables

While creating a local movie database, I have 3 tables with below structure:

TABLE1: movies
movie_id | movie_title

TABLE2: categories
category_id | category_title

TABLE3: categories_movies
id | movie_id | category id


1 movie can have multiple categories, I'm trying to display each movie with its categories, example:

MOVIE TITLE 1 (Category 1, Category 2, Category 3)
MOVIE TITLE 2 (Category 2, Category 3)


Currently I do with 3 queries, first I select all movies:

select * from movies


Then

select * from categories_movies where movie_id = $movie_id


Then

select * from categories where category_id = $category_id


I've tested with some inner join, but no good idea, since 1 movie can have multiple categories.

Any suggestion will help, thanks.

Answer

I didn't fully understand what is the format you want to display it, but you can use GROUP_CONCAT() if you want to display it as 1 column:

SELECT m.movie_title,GROUP_CONCAT(c.category_title)
FROM movies m
INNER JOIN categories_movies cm 
 ON m.movie_id = cm.movie_id
INNER JOIN categories c
 ON cm.category_id = c.category_id
GROUP BY m.movie_title

If you just want them to be displayed as separate rows:

SELECT m.movie_title,c.category_title
FROM movies m
INNER JOIN categories_movies cm 
 ON m.movie_id = cm.movie_id
INNER JOIN categories c
 ON cm.category_id = c.category_id