CODEWITHSUNDEEP
mysqlmongodbmany-to-manydenormalization
Murlidhar Fichadia
Murlidhar Fichadia

Reputation: 2609

denormalize many to many relationship in MySQL

I would like to denormalize many to many relationship in mysql. In order to import to MongoDB as Json format Schema.

Input

I have 3 tables:

Movies : id, title, url
Genres : id, genre
movie_genres : movie_id, genre_id

example

movie Table
id   title       link                    
1    star wars   http://link-to-imdb   
2    shrek       http://link-to-imdb  

movie_genres Table
movie   genre                    
1       1 
2       1

genres Table
id      genre                    
0       unknown 
1       action 
2       comedy 
3       drama

I would like to transform it to a single table by moving genres into movies as array or multiple values.

There are quite a few limited number of genres (only 15).

Output

So, Final output of table would be:

Movies : id, title, url, genre

Here, genre would be multiple values.

Example:

id   title       link                  genre  
1    star wars   http://link-to-imdb   action, drama, sci-fi
2    shrek       http://link-to-imdb   anime

I did this - MySQL Query:

select M.id ,M.title ,M.release_date, M.video, M.IMDBURL, G.genre 
from genres G, movie_genres MG, movies M 
where M.id = MG.movie and MG.genre = G.id

but causes lot of repetition depending on number of genres. It would be nice If I could dump genres altogether.

Upvotes: 0

Views: 1489

Answers (1)

Oleks
Oleks

Reputation: 1633

In this cause you should use GROUP_CONCAT() function

SELECT movie.id, movie.title, movie.url, GROUP_CONCAT(g.genre SEPARATOR ', ') AS genres
FROM movie
LEFT JOIN movie_genres mg ON movie.id = mg.movie_id
LEFT JOIN genres g ON mg.genre_id = g.id
GROUP BY movie.id

I didn't test the query above (there could be some typos), but I hope you will be able to get the idea

Upvotes: 2

Related Questions