Using mysql joins across 3 tables

Question

I can't seem to figure out what's wrong with my syntax for performing a full join in mysql

select movies.title, genres.name    
from movies
full join genres_in_movies
on movies.id = genres_in_movies.movie_id
full join genres
on genres_in_movies.genre_id = genres.id
where movies.id = 12345;

Here are the relevant tables:

genres
id (pk)
name 

movies
id (pk)
title

genres_in_movies
genre_id
movie_id

I'm trying to get a table listing movies and their genres. A single movie may have multiple entries in the genres table, so I'm using a full join to get all the results I can without having to repeat the movies.title attribute in the final result.

I'm using mysql, if that makes any difference. The error I get complains about an error in my syntax around line 5.

EDIT:

Ok, well a sample output as is now:

Name    Genre
Batman  Action
Batman  Crime
Batman  Mystery

I want to get a table that would not hold 3 rows to hold this information, but instead have something like:

Title  Genre
Batman Action, Crime, Mystery

Or is that impossible?

Answer 1

MySQL does not support FULL JOIN. You have to use a combination of left outer join and right outer join using a UNION clause.

You can see an explanation here.

Anyway, I don´t think a FULL JOIN will give you what you are trying to get.

In order to avoid having more than one record for each movie, you should group by movie, but then you would have to do a group concat to show all the genres in a single record.

Please provide a sample expected output for your query.

Edit:

select movies.title, GROUP_CONCAT( genres.name SEPARATOR ‘, ’ ) as 'genres'
from movies
inner join genres_in_movies on movies.id = genres_in_movies.movie_id
inner join genres on genres_in_movies.genre_id = genres.id
where movies.id = 12345
group by movies.title;

Answer 2

try this one:

SELECT m.title, g.name
FROM movies m
JOIN genre_in_movies gim on gim.movie_id = m.id
JOIN genres g on g.id = gim.movie_id
WHERE m.id = '12345'