Search code examples
mysqlconcatenationgroup-concat

Two different concatenates based on the same table

I try to design a mysql query to get results from a Greek movie database like: Title, concatenated Directors and concatenated awarded people. So, I use:

SELECT 
     `MT_films`.`ID_films` AS `ID_films`,
    `ST_br_persons`.`etos_brp` AS `year_award`,  
    `T_brab_p`.`brabeio_p` AS `price`,
    `T_thesmos`.`thesmos` AS `festival`,
    `T_idiotita`.`idiotita` AS `specialty`,
    CONCAT('<a href="./index.php/search-movies/titlededj?id_films=',
            `MT_films`.`ID_films`,
            '">',
            `MT_films`.`Title1`,
            '</a>') AS `movie`,
    GROUP_CONCAT(DISTINCT '<a href="./index.php/search-movies/peoplefilm?person=',
        `ST_br_persons`.`ID_person`,
        '">',
        `T_people`.`person`,
        '</a>'
        SEPARATOR ', ') AS `awarded_person`,
    GROUP_CONCAT(CASE
            WHEN
                `ST_peoplefilms`.`ID_idiotita` = 29 --> director specialty filter
            THEN
                CONCAT('<a href="./index.php/search-movies/peoplefilm?person=',
                        `ST_peoplefilms`.`ID_person`,
                        '">',
                        `T_people`.`person`,
                        '</a>')
        END
        SEPARATOR ', ') AS `Director`
FROM
    (((((((`T_people` `T_people_1`
    JOIN `ST_peoplefilms` `ST_peoplefilms_1` ON (`T_people_1`.`ID_person` = `ST_peoplefilms_1`.`ID_person`))
    JOIN `MT_films` ON (`MT_films`.`ID_films` = `ST_peoplefilms_1`.`ID_films`))
    JOIN `ST_br_persons` ON (`ST_br_persons`.`ID_person` = `T_people_1`.`ID_person`))
    JOIN `T_idiotita` ON (`T_idiotita`.`ID_idiotita` = `ST_br_persons`.`ID_Idiotita`))
    JOIN `T_thesmos` ON (`T_thesmos`.`ID_thesmos` = `ST_br_persons`.`ID_thesmos`))
    JOIN `T_brab_p` ON (`T_brab_p`.`ID_brab_p` = `ST_br_persons`.`ID_brabeio`))
    JOIN `T_people` ON (`T_people`.`ID_person` = `ST_br_persons`.`ID_person`))
WHERE
    `ST_br_persons`.`ID_thesmos` IN (12 , 270, 446, 447, 448, 449, 565, 566)
        AND `ST_br_persons`.`ID_Films` = `ST_peoplefilms_1`.`ID_films`
        AND `MT_films`.`ID_films` = 12429


GROUP BY `ST_br_persons`.`ID_brabeio` , `ST_br_persons`.`ID_Films`
ORDER BY `ST_br_persons`.`year_award`

Obviously all films have a director and in some of them there are award-winning persons from other qualities e.g. Screenplay award, scenography award etc etc...

Query works fine for all the awarded people, but the problem is that it returns null in the director's field.

ID_films year_award price Festival movie awarded_person Director
2677 2021 Best scenery DISFF IF I KNEW John null
2678 2023 Best actress AAISFF LOVEHATERS Eva, Georgia null
13459 1985 Best scenography FFGR CAT POSTALE Steve, Maria, Kostas null

But when the director of a film is awarded with the Best director's award then the Director field displays his name normally

ID_films year_award price Festival movie awarded_person Director
13741 2020 Best directing xxx1 BASTARDS Panos Panos
11213 2012 Best directing xxx2 LISTEN Chris, Liza Chris, Liza
1256 2015 Best directing xxx3 CAT POSDODO Katerina Katerina

Desired outcome

ID_films year_award price Festival movie awarded_person Director
2677 2021 Best scenery DISFF IF I KNEW John Kostas
2678 2023 Best actress AAISFF LOVEHATERS Eva, Georgia Yiannis
13459 1985 Best scenography FFGR CAT POSTALE Steve, Maria, Kostas Alexis, Vangelis
Solution

  • Because the director of a film may, or may not, be in the list of awarded_people, they may, or may not, be joined via st_br_persons. And, because there may be multiple directors and awarded_people, adding another join to get the director(s) could result in a cartesian product between the two. Instead I have retrieved the director(s) with a correlated subquery in the select list. I have also added the additional columns to the GROUP BY clause to make the query valid (read MySQL Handling of GROUP BY). I have also removed aa_st_br_person from the select list as it did not make sense as a row can aggregate multiple awarded people. To make it valid you would either need to use an aggregate function or add it to the GROUP BY clause.

    SELECT 
    f.ID_films,
    br_p.etos_brp AS year_award,
    b.brabeio_p AS award,
    t.thesmos AS festival,
    f.Title1 AS movie,
    GROUP_CONCAT(p.ID_person, '::', p.person SEPARATOR '||') AS awarded_people,
    (
        SELECT GROUP_CONCAT(p.ID_person, '::', p.person SEPARATOR '||')
        FROM t_people p
        JOIN st_peoplefilms pf ON p.ID_person = pf.ID_person AND pf.ID_idiotita = 29
        WHERE pf.ID_films = f.ID_films
    ) AS director
FROM st_br_persons br_p
JOIN t_people p
    ON br_p.ID_person = p.ID_person
JOIN mt_films f
    ON br_p.ID_films = f.ID_films
JOIN t_thesmos t
    ON br_p.ID_thesmos = t.ID_thesmos
JOIN t_brab_p b
    ON br_p.ID_brabeio = b.ID_brab_p

WHERE br_p.ID_thesmos IN (12 , 270, 446, 447, 448, 449, 565, 566)
  
GROUP BY
    br_p.ID_brabeio,
    br_p.etos_brp,
    br_p.ID_Films,
    br_p.ID_thesmos
ORDER BY br_p.etos_brp DESC;

    Output:

    ID_films year_award award festival movie awarded_people director
    14365 2023 Screenplay Award DISFF National Competition AEROLIN 34980::Koukias Pantelis Alexis||43644::Mathioudakis Michalis 34980::Koukias Pantelis Alexis
    14365 2023 Direction Award DISFF National Competition AEROLIN 34980::Koukias Pantelis Alexis 34980::Koukias Pantelis Alexis
    12429 2021 Scenography Award DISFF National Competition BRUTALIA, DAYS OF LABOUR 18238::Kalogianni Dafni 36433::Mavris Manolis
    12429 2021 Costume Design Award DISFF National Competition BRUTALIA, DAYS OF LABOUR 36437::Gioulakou Eva 36433::Mavris Manolis

    Here's a db<>fiddle to play with.