多表连接去重复 mysql_连接多个表时避免重复条目(MySQL)

(请参阅我在本文底部测试的数据库结构.)

我执行这个查询：

SELECT m.title, GROUP_CONCAT(DISTINCT(d.name) SEPARATOR ',') d FROM movies m

INNER JOIN movies_seen s

ON s.object_id = m.id

LEFT JOIN movies_directors_connections dc

ON dc.movie_id = m.id

LEFT JOIN movies_directors d

ON d.id = dc.director_id

有了这个结果：

title | d

Pulp Fiction | Quentin Tarantino,George Butler,Robert Fiore

但我想要得到这个：

title | d

Pulp Fiction | Quentin Tarantino

Pumping Iron | George Butler,Robert Fiore

和建议？ 🙂

CREATE TABLE `movies` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`title` varchar(90) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=3 ;

CREATE TABLE `movies_seen` (

`object_id` int(10) NOT NULL DEFAULT '0',

`date` varchar(10) NOT NULL DEFAULT '0');

CREATE TABLE `movies_directors` (

`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(255) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=4 ;

CREATE TABLE IF NOT EXISTS `movies_directors_connections` (

`movie_id` mediumint(8) unsigned NOT NULL DEFAULT '0',

`director_id` mediumint(8) unsigned NOT NULL DEFAULT '0'

) ENGINE=MyISAM;

然后是一些测试数据：

INSERT INTO `movies` (`id`, `title`) VALUES

(1, 'Pulp Fiction'), (2, 'Pumping Iron');

INSERT INTO `movies_seen` (`object_id`, `date`) VALUES

(1, 1359511222), (2, 1359511223);

INSERT INTO `movies_directors` (`id`, `name`) VALUES

(1, 'Quentin Tarantino'),

(2, 'George Butler'),

(3, 'Robert Fiore');

INSERT INTO `movies_directors_connections` (`movie_id`, `director_id`) VALUES

(1, 1), (2, 2), (2, 3);