Using MySQL, join two tables by updating the values in the result table

I have the following schema:

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');

There are some set of rules given to obtain the result. They are as follows

Set of conditions:

  1. Pick the tag of a director if it's there for a song and ignore tags by asst_directors
  2. For a given song only one director tag record would be there
  3. For a given song, 3 tag records can be there by asst_directors. So total 4 records max if no director tag is available then pick the tag which was provided by more than 1 tagged_role.
  4. If all editors have provided diff tag in tagged role then show 'MIX-GENRE' as the tagged_role so that director can review and fix.
  5. If there is only one asst_director record then we can show that tag directly.

I tried the below query. But I am unable to satisfy all given conditions

Solution I tried:

select songs.song_id, 
       song_name, 
       genre_tag,
       case when genre_tag is null 
            then ''
            when tagged_role='director' 
            then 'director'
            when (count(distinct genre_tag) > 1) 
            then (select max(tagged_role) 
                  from genre_tag_information)
            when tagger_role <> 'director' and (count(distinct genre_tag)) = 1 
            then tagged_role
            else 'no entry'
            end as 'director_action_role'
from sentenses
left join tags on tags.sentense_id = sentenses.sentense_id
group by tags.song_id;

Output expected:

song_id | song_name | genre_tag | director_tag_role |

 1    |  abcd     |  folk      |  director       |  
 2    |  efgh     |  classic   |  asst_director  |  
 3    |  ijkl     |            |                 |      
 4    |  mnop     |  mix_genre |  asst_director< |

1 answer

  • answered 2021-05-17 06:42 Akina

    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
    

    https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=ede18a831e86e9853971e0c858a61e76