MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table songs
(song_id int not null primary key auto_increment
, song_name varchar(500)
);

create table genre_tag_information
(singer_id int primary key not null auto_increment
, song_id int
, genre_tag varchar(20)
, tagged_role varchar(20)
, foreign key(song_id) references songs(song_id)
);

insert into songs (song_name)
values
('abcd')
, ('efgh')
, ('ijkl')
, ('mnop');

insert into genre_tag_information (song_id, genre_tag, tagged_role)
values
(1, 'folk', 'director')
, (1, 'folk', 'asst_director')
, (1, 'folk','asst_director')
, (2, 'classic', 'asst_director')
, (2, 'classic', 'asst_director')
, (2, 'melody', 'asst_director')
, (4, 'rock', 'asst_director')
, (4, 'jazz', 'asst_director')
, (4, 'bass', 'asst_director');
SELECT song_id,
       SUM(tagged_role = 'director'),
       COUNT(DISTINCT genre_tag) < COUNT(*)
FROM genre_tag_information
GROUP BY song_id


song_id
SUM(tagged_role = 'director')
COUNT(DISTINCT genre_tag) < COUNT(*)


1
1
1

2
0
1

4
0
0


SELECT song_id, genre_tag, tagged_role, COUNT(*) OVER (PARTITION BY song_id, tagged_role, genre_tag) cnt
FROM genre_tag_information
ORDER BY song_id


song_id
genre_tag
tagged_role
cnt


1
folk
asst_director
2

1
folk
asst_director
2

1
folk
director
1

2
classic
asst_director
2

2
classic
asst_director
2

2
melody
asst_director
1

4
bass
asst_director
1

4
jazz
asst_director
1

4
rock
asst_director
1


WITH 
cte1 AS ( SELECT song_id, 
                 genre_tag, 
                 tagged_role, 
                 COUNT(*) OVER (PARTITION BY song_id, tagged_role, genre_tag) cnt
          FROM genre_tag_information
          ORDER BY song_id ),
cte2 AS ( SELECT song_id, 
                 genre_tag, 
                 tagged_role, 
                 ROW_NUMBER() OVER (PARTITION BY song_id ORDER BY tagged_role = 'director' DESC, cnt ASC) rn,
                 MAX(cnt > 1) OVER (PARTITION BY song_id) mix
          FROM cte1 )
SELECT songs.song_id,
       songs.song_name,
       CASE WHEN cte2.mix IS NULL
            THEN ''
            WHEN cte2.mix = 1
            THEN cte2.genre_tag
            ELSE 'mixed' 
            END genre_tag, 
       COALESCE(cte2.tagged_role, '') tagged_role
FROM songs
LEFT JOIN cte2 ON songs.song_id = cte2.song_id AND rn = 1


song_id
song_name
genre_tag
tagged_role


1
abcd
folk
director

2
efgh
melody
asst_director

3
ijkl



4
mnop
mixed
asst_director

song_id	genre_tag	tagged_role	cnt
1	folk	asst_director	2
1	folk	asst_director	2
1	folk	director	1
2	classic	asst_director	2
2	classic	asst_director	2
2	melody	asst_director	1
4	bass	asst_director	1
4	jazz	asst_director	1
4	rock	asst_director	1

song_id	song_name	genre_tag	tagged_role
1	abcd	folk	director
2	efgh	melody	asst_director
3	ijkl
4	mnop	mixed	asst_director