sql - Mysql : Left join on multiple rows and retrieve 1 row

I have 2 tables:

Table : Movies

MovieID -- Name
1          -- Movie1
2          -- Movie2

Table: Types

MovieID -- Type
1          -- DVD
1          -- Bluray
1          -- VCD
2          -- DVD

I need a query to find out this in one row: Movie1 : DVD - Bluray - VCD

I used:

SELECT Movies.Name,
IF(TYPE = 'DVD', 1, 0 ) AS DVD,
IF(TYPE = 'Bluray', 1, 0 ) AS Bluray,
IF(TYPE = 'VCD', 1, 0 ) AS VCD
FROM Movies LEFT JOIN Types ON Movies.MovieID = Types.MovieID

But it return multiplate lines:

Movies.Name -- DVD -- Bluray -- VCD
Movie1          -- 1     -- 0        -- 0
Movie1          -- 0     -- 1        -- 0
Movie1          开发者_StackOverflow-- 0     -- 0        -- 1
Movie2          -- 1     -- 0        -- 0

I want:

Movie1          -- 1     -- 1        -- 1
Movie2          -- 1     -- 0        -- 0

The group_concat() function might do the trick, here.

Not tested, but I suppose something like this should work :

SELECT Movies.Name,
    group_concat(type separator ' - ') as type
FROM Movies 
    LEFT JOIN Types ON Movies.MovieID = Types.MovieID
group by Movies.Name

Assuming you want separate columns returned for each type:

SELECT m.Name, 
       SUM(CASE WHEN t.Type = 'DVD' THEN 1 ELSE 0 END) AS DVD,
       SUM(CASE WHEN t.Type = 'Bluray' THEN 1 ELSE 0 END) AS Bluray,
       SUM(CASE WHEN t.Type = 'VCD' THEN 1 ELSE 0 END) AS VCD
    FROM Movies m
        LEFT JOIN Types t 
            ON m.MovieID = t.MovieID
    GROUP BY m.Name

SELECT m.Name,
IF(t1.Type IS NOT NULL, 1, 0) as DVD,
IF(t2.Type IS NOT NULL, 1, 0) as Bluray,
IF(t3.Type IS NOT NULL, 1, 0) as VCD
FROM Movies m
LEFT OUTER JOIN Types t1 on m.MovieID = t1.MovieID and t1.Type = 'DVD'
LEFT OUTER JOIN Types t2 on m.MovieID = t2.MovieID and t2.Type = 'Bluray'
LEFT OUTER JOIN Types t3 on m.MovieID = t3.MovieID and t3.Type = 'VCD'