I have been searching for ways to do this but I was not able to find any.
What I want to do is this:
movie_genre (Table)
movidId movieTitle movieGenre (Varchar)
1 Copying Beethoven (2006) Biography
2 Copying Beethoven (2006) Drama
3 Copying Beethoven (2006) Music
movie_plot (Table)
movieId movieTitle moviePlot
1 Copying Beethoven (2006) A fictionalized account of the last year of...
I would like to merge them into a single table:
movie_title (PK)
movie_release_year (YEAR)
movie_plot (TEXT)
movie_genre
(SET('Action','Adventure','Adult','Animation','Comedy','Crime','Documentary','Drama','Fantasy','Family','Film-Noir','Horror','Musical','Mystery','Romance','Sci-Fi','Short','Thriller','War','Western'))
movie_average_rating (FLOAT)
I am temporarily using the following statement:
SELECT (SELECT DISTINCT movie_genre.movieTitle), movie_genre.movieReleaseYear, movie_genre.movieGenre, movie_plot.plot FROM movie_genre LEFT OUTER JOIN movie_plot ON movie_genre.movieTitle = movie_plot.movie_title WHERE movieTitle = '"Weeds" (2005)';
I am using MySQL & InnoDB. Executing the query above gives me redundant results (3 rows with same title, release year and plot entity but different genres). I would like to merge them into a single table. If this is not possible, is there a way to merge rows in (movie_genre) table?
movie_genre (Table)
movidId movieTitle movieGenre (Varchar)
1 Copying Beethoven (2006) Biography
2 Copying Beethoven (2006) Drama
3 Copying Beethoven (2006) Music
into
movie_genre (Table)
movidId movieTitle movieGenre (Varchar)
1 Copying Beethoven (2006) Biography, Drama, Music