Clash Royale CLAN TAG

How relation between one record on a table and several records on onother table

My project is a program like movie collection.
Main table is for storing movie data with fields like name,director,release year and ...
But each record must have another attribute named genre (genres).
Each movie may be categorized by one or several genres (Comedy, Horror, History, Romance,...)
Genre table contain two fields.ID:integer and Genre:vchar
For each movie, some of genre fields can be selected.
How I can do this?
thank you

sql join

2 Answers
2

The movie table must have some movie_id in order to uniquely identify each movie.
It must also have a column called genre_id (same as that in the genre table) to link the movie with each genre.

movie

movie_id

genre_id

genre

Using the following query, you can choose movie belonging to each genre:

SELECT M.movie_id
,M.movie_name
,G.genre_id
FROM movie M
LEFT JOIN genre G ON M.genre_id = G.genre_id


You need 3 tables to do this work correctly.

Table Moovie :
Moov_ID
Moovie_Name
Moov_DirectorID
...

Table Genres:
Genre_ID
Genre_Name
...


Table Moovie_Genre (Whic will link moovies and genres)
Mg_ID
Mg_MoovieID
Mg_GenreID


Like this you can have a many to many relation.

You can use a string field in your moovie table in order to use comma seperated values to stock genreIDs but, I prefere to have a many to many relation.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.