1

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

Abolfazl Delbari
  • 11
  • 1
  • you have to use `sql join` for that, but before this you have to set relationship of primary key table and foreign key table – er-sho Jul 27 '18 at 06:50
  • Possible duplicate of [How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?](https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de) You will need a many-to-many relationship. It is not one-to-many because many movies can have the same genre and a movie can have more than one genre. – Georg Patscheider Jul 27 '18 at 06:53

3 Answers3

1

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.

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
AswinRajaram
  • 1,519
  • 7
  • 18
1

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.

Coskun Ozogul
  • 2,389
  • 1
  • 20
  • 32
1

Just to expand on Oskon's answer; The script might look like this: (I've used temporary tables here - just remove the # to make them permanent)

----create the tables
CREATE TABLE #tblMovie 
  (
      MovieID int NOT NULL
      ,MovieName varchar(100) NULL
      ,MovieDirectorID int NULL
      ----add more fields here if you want
  )

  CREATE TABLE #tblGenre
  (
      GenreID int NOT NULL
      ,GenreName varchar(100) NULL
  )

  CREATE TABLE #tblMovieGenre ----this is where the genres of the movies are listed
  (
      MovieGenreID int NOT NULL --id for this table (just for indexing)
      ,MovieID int NOT NULL --movie id from #tblmovie
      ,GenreID int NOT NULL --genre id from #tblGenre
  )

  ----insert the information
  INSERT INTO #tblMovie (MovieID,MovieName,MovieDirectorID)
  VALUES (1,'Movie 1 Name',3)
  ,(2,'Movie 2 Name',10)
  ,(3,'Movie 3 Name',2)

  INSERT INTO #tblGenre (GenreID, GenreName)
  VALUES (1,'Comedy')
  ,(2,'Horror')
  ,(3,'Action')
  ,(4,'Thriller')
  ----add more genres 

  INSERT INTO #tblMovieGenre (MovieGenreID,MovieID,GenreID)
  VALUES (1,1,1) --movie 1 is a comedy
  ,(2,2,3) --movie 2 is an action movie
  ,(3,3,3),(3,3,4) --movie 3 is an action/thriller movie

  ----Now find the genres for a movie (run this bit as many times as you want)
  SELECT 
  T3.GenreName 

  FROM
  #tblMovie T1
  INNER JOIN #tblMovieGenre T2
  ON T1.MovieID = T2.MovieID
  LEFT OUTER JOIN #tblGenre T3
  ON T2.GenreID = T3.GenreID

  WHERE
  T1.MovieName = 'Movie 3 Name' --put your movie name here
Sanchez333
  • 318
  • 3
  • 11