How to select multiple entrys of table B that have a relation to an entry of table A

Question

I have a Table "Movie" and a Table "Director". Those are connected by the Table "MovieDirector" with a multiple to multiple relation. The Tables have the following fields:

Movie:
uid
name
release_date

Director:
uid
name

MovieDierector:
uid
movie_id
director_id

I now want a list of all movies with their directors. It is possible, that more than one directors worked on a movie. If this is the case, I want the result to look like this:

movie   | release-date | director
movie A | 2011         | director 1, director 2
movie B | 2013         | director 1
movie C | 1999         | director 3

I have tried inner join, left join ,right join in all possible combinations. But I always get this result:

movie   | release-date | director
movie A | 2011         | director 1
movie A | 2011         | director 2
movie B | 2013         | director 1
movie C | 1999         | director 3

All that changes with the different join combinations is the ordering of the entries.

Can someone tell me, if this is even possible to achieve with MySQL? If yes, how is it done? And if not how would be the best way to achieve this with multiple SQL requests?

You can `GROUP BY` movie id and `GROUP_CONCAT()` the directors. See: https://stackoverflow.com/a/3935730/3986005 — KIKO Software, Jun 14 '19 at 10:03
Sample data? see [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) — Raymond Nijland, Jun 14 '19 at 10:04

score 1 · Accepted Answer · edited Jun 14 '19 at 10:26

1

You can get this done by using GROUP BY with aggregate function GROUP_CONCAT() as following

SELECT Movie.uid, 
       Movie.name, 
       Movie.release_date, 
       GROUP_CONCAT(Director.name) AS directors 
FROM Movie 
INNER JOIN MovieDierector on Movie.uid = MovieDierector.movie_id 
INNER JOIN Director ON Director.uid = MovieDierector.director_id 
GROUP BY Movie.uid

edited Jun 14 '19 at 10:26

Alberto Moro

1,014
11
22

answered Jun 14 '19 at 10:07

Sandesh Jain

704
3
13

@th3r3alpr0 This query is most likely (very) wrong this is not in general how you should write [GROUP BY](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html) queries.. As SQL-92 and earlier does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause.. This query is only valid when [functionally dependency](https://dev.mysql.com/doc/refman/5.7/en/group-by-functional-dependence.html) could be used meaning `Movie.uid` should have a `PRIMARY` or `UNIQUE` key – Raymond Nijland Jun 14 '19 at 10:15
2

No, it doesn't work with UPDATE or CREATE. GROUP BY can only be combined with SELECT. Then again: Why would you want that? – KIKO Software Jun 14 '19 at 10:22
@KIKOSoftware I just wanted to make as less SQL requests as necessary. But if it doesn't work with UPDATE or CREATE I will just make multiple requests in the script. For the Select i will need it to be able to use WHERE LIKE to make possible to search for certain entries. – th3r3alpr0 Jun 14 '19 at 11:30

score 1 · Answer 2 · answered Jun 14 '19 at 10:20

1

SELECT `Movie`.`name`                                  AS `movie`,
       `release_date`                                  AS `release-date`,
        GROUP_CONCAT(`Director`.`name` SEPARATOR ', ') AS `director`
FROM `MovieDierector`
JOIN `Movie` ON `MovieDierector`.`movie_id` = `Movie`.`uid`
JOIN `Director` ON `MovieDierector`.`director_id` = `Director`.`uid`
GROUP BY `Movie`.`uid`

You can test here

answered Jun 14 '19 at 10:20

Alberto Moro

1,014
11
22

It is a bit unusual (for me) to start with the `MovieDierector` table, but it does indeed work, and the test is very helpful. – KIKO Software Jun 14 '19 at 10:26

How to select multiple entrys of table B that have a relation to an entry of table A

2 Answers2