5

(please see the database structure I'm testing with at the bottom of this post.)

I execute this query:

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

With this result:

title        | d 
Pulp Fiction | Quentin Tarantino,George Butler,Robert Fiore

But I'm trying to get this:

title        | d 
Pulp Fiction | Quentin Tarantino
Pumping Iron | George Butler,Robert Fiore

And suggestions? :)

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;

And then some test data:

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);
1
  • Hmm, why does this table structure seem awfully familiar? ;-) Commented Jan 30, 2013 at 12:43

1 Answer 1

7

you just need to add GROUP BY clause

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
GROUP   BY m.title

OTHER LINK

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.