I am working on a home project. In my database I have two table called movies and category. in the movies table there are three rows named category,category_two,category_three. And in the category table there are only one row name category_name. I wanted to join all three rows from the movies table with the row named category_table from category table. but I do not know how to do that query. Please help me.
Asked
Active
Viewed 158 times
0
-
http://stackoverflow.com/questions/12526194/mysql-inner-join-select-only-one-row-from-second-table – Ashwin Golani Mar 16 '17 at 13:02
-
1Possible duplicate of [sql join joining 3 tables](http://stackoverflow.com/questions/37801299/sql-join-joining-3-tables) – Masivuye Cokile Mar 16 '17 at 13:04
1 Answers
1
This is how you should do it:
movies table
------------
id
name
categories table
----------------
id
name
movie_categories table
----------------------
movie_id
category_id
Like this you can have any amount of categories for a movie. Then to get a specific movie along with its categories you can do:
select m.*, c.name as category_name
from movies m
left join movie_categories mc on m.id = mc.movie_id
left join categories c on c.id = mc.category_id
where m.name = 'star wars'
juergen d
- 201,996
- 37
- 293
- 362