CODEWITHSUNDEEP
phpmysqlmysqli
Prince Mahmud Hridoy
Prince Mahmud Hridoy

Reputation: 41

Join three rows with one row from another table

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.

Upvotes: 0

Views: 171

Answers (1)

juergen d
juergen d

Reputation: 204794

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'

Upvotes: 1

Related Questions