使用join从两个表中选择多个条目

I want to produce a result which would provide the following result from the databases given below.

我想产生一个结果,该结果将从下面给出的数据库中提供以下结果。

20  'someVideo'  'Jason Statham, Tom Cruise, Bruce Wills, Anne Hathaway'  'musicDirector'  'Director

Please note that the actors are separated with ", ". Thanks in advance

请注意,演员用“,”分隔。提前致谢

term_relationships
ID video_id related_id type
1 20 5 actor
1 20 2 actor
1 20 3 actor
1 20 4 actor
1 20 10 music
1 20 11 director

term_relationships ID video_id related_id type 1 20 5 actor 1 20 2 actor 1 20 3 actor 1 20 4 actor 1 20 10 music 1 20 11 director

actors
ID TITLE
2 Tom Cruise
3 Bruce Wills
4 Anne Hathaway
5 Jason Statham

演员ID标题2汤姆克鲁斯3布鲁斯威尔斯4安妮海瑟薇5杰森斯坦森

musics
ID TITLE
10 musicDirector

音乐ID TITLE 10 musicDirector

directors
ID TITLE
10 Director

董事ID TITLE 10董事

videos
ID TITLE
20 SomeVideo

视频ID标题20 SomeVideo

2 个解决方案

#1

Andrea's answer with GROUP_CONCAT function is brilliant and works. However, it should be perfected by also adding a type-filter for actors like this:

Andrea对GROUP_CONCAT功能的回答很棒并且有效。但是,应该通过为这样的actor添加类型过滤器来完善它:

select tr.video_id, v.title, 
  GROUP_CONCAT(DISTINCT(a.title) SEPARATOR ',') actors,
  GROUP_CONCAT(DISTINCT(d.title) SEPARATOR ',') director,
  GROUP_CONCAT(DISTINCT(m.title) SEPARATOR ',') music
FROM term_relationships tr 
  INNER JOIN videos v on v.id = tr.video_id 
  LEFT JOIN actors a on tr.related_id = a.id and tr.type = 'actor'
  LEFT JOIN musics m on tr.related_id = m.id and tr.type = 'music' 
  LEFT JOIN directors d on tr.related_id = d.id and tr.type = 'director' 
WHERE tr.video_id = 20 
GROUP BY tr.video_id;

#2

To combine the actor names into a comma-separated list, you can use the mysql GROUP_CONCAT function. Check if this SQL statement gets you what you want:

要将actor名称组合成逗号分隔列表,可以使用mysql GROUP_CONCAT函数。检查此SQL语句是否能满足您的需求:

Select tr.video_id, videos.title AS video, 
   GROUP_CONCAT(DISTINCT(actors.title) SEPARATOR ',') AS actor_names,  
   GROUP_CONCAT(DISTINCT(directors.title) SEPARATOR ',') AS director,   
   GROUP_CONCAT(DISTINCT(musics.title) SEPARATOR ',') AS music 
FROM term_relationships tr inner join videos on videos.id = tr.video_id 
LEFT JOIN actors on tr.related_id = actors.id 
LEFT JOIN musics on tr.related_id = musics.id and tr.type = 'music' 
LEFT JOIN directors on tr.related_id = directors.id and tr.type = 'director' 
WHERE tr.video_id = 20 GROUP BY tr.video_id

#1