Two different concatenates based on the same table

Question

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

Answer 1

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.