conditional sql query for tables

Question

I have to run a query from SQL. Perhaps too easy or not, but unfortunately I do not understand SQL. So here's my tables:

films

id | movie_name
------------
1  | prometheus

names

id | person_name
-----------------
1  | Ridley Scott
2  | Noomi Rapace
3  | Michael Fassbender
4  | Charlize Theron
5  | Damon Lindelof

films_cast

id | film_id | person_id | type
-------------------------------
1  | 1       | 1         | director
2  | 1       | 2         | actor
3  | 1       | 3         | actor
4  | 1       | 4         | actor
5  | 1       | 5         | writer

And how can I reach such a conclusion from these tables:

film_archive

id | movie_name | director     | writer         | cast
---------------------------------------------------------------------------------------
1  | prometheus | Ridley Scott | Damon Lindelof | Noomi Rapace, Michael Fassbender, Charlize Theron

Thank you!

score 6 · Answer 1 · answered Sep 16 '12 at 11:53

6

You need to join your tables, then group by film performing GROUP_CONCAT() aggregation on the names column given a suitable condition:

SELECT   f.id,
         f.movie_name,
         GROUP_CONCAT(IF(c.type='director', n.person_name, NULL)) AS director,
         GROUP_CONCAT(IF(c.type='writer',   n.person_name, NULL)) AS writer,
         GROUP_CONCAT(IF(c.type='actor',    n.person_name, NULL)) AS cast
FROM     films f LEFT JOIN films_cast c ON c.film_id   = f.id
                 LEFT JOIN names n      ON c.person_id = n.id
GROUP BY f.id

See it on sqlfiddle.

answered Sep 16 '12 at 11:53

eggyal

122,705
18
212
237

1

You did this much faster than I could! – Korvin Szanto Sep 16 '12 at 11:54
How do I export this results into a CSV/TXT file? – jean grey Sep 16 '12 at 11:58
@jeangrey: Before the `FROM` clause, add `INTO OUTFILE '/path/to/file.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'`. – eggyal Sep 16 '12 at 11:59

score 2 · Answer 2 · edited May 23 '17 at 12:07

Since you also have the sql-server tag on your question, unfortunately sql-server doesn't have GROUP_CONCAT, an alternative like FOR XML / STUFF is needed:

SELECT f.id, f.movie_name,
       STUFF((SELECT ',' + p.person_name
              FROM persons p 
                INNER JOIN films_cast fc on fc.person_id = p.id
                WHERE fc.film_id = f.id AND fc.type = 'director'
              FOR XML PATH (''))
             , 1, 1, '') AS director,
        STUFF((SELECT ',' + p.person_name
              FROM persons p 
                INNER JOIN films_cast fc on fc.person_id = p.id
                WHERE fc.film_id = f.id AND fc.type = 'writer'
              FOR XML PATH (''))
             , 1, 1, '') AS writer,
        STUFF((SELECT ',' + p.person_name
              FROM persons p 
                INNER JOIN films_cast fc on fc.person_id = p.id
                WHERE fc.film_id = f.id AND fc.type = 'actor'
              FOR XML PATH (''))
             , 1, 1, '') AS cast
    FROM films f;

SQLFiddle here

score 1 · Answer 3 · answered Sep 16 '12 at 11:54

Try this,

SELECT      a.id, a.movie_name,
            GROUP_CONCAT((CASE WHEN b.type = 'director' THEN c.person_name ELSE NULL END)) as `director`,
            GROUP_CONCAT((CASE WHEN b.type = 'writer' THEN c.person_name ELSE NULL END)) as `writer`,
            GROUP_CONCAT((CASE WHEN b.type = 'actor' THEN c.person_name ELSE NULL END)) as `cast`
FROM        films a
            LEFT JOIN film_cast b
                ON a.id = b.film_id
            LEFT JOIN names c
                ON b.person_id = c.id
GROUP BY    a.id, a.movie_name

conditional sql query for tables

3 Answers3

SQLFiddle Demo