Been trying to reintroduce myself to SQL through some practice questions I’ve developed for myself, but struggling to find a better way of approaching the following problem:
id title 1 Title1 2 Title2
id playlist_id clip_id 1 Title1 3 2 Title2 1
playlist_id tag_id 1 1 1 2 2 2
Tags are two entirely separate tables, and I am using the
playlist_clips to connect them to the
playlists table, to represent the two-way one-to-many relationships.
I wanted to select all the playlists that have a given title, and have ALL of the tags provided in the query (in this example [1, 2]), not just “at least one of them”; additionally, I want to query information from the playlist_clips table, including the number of clips, total playlist duration, etc.
This is what I’ve come up with:
select p_clips.* from ( p.id, p.title, CAST(COALESCE(sum(pc.duration_seconds), 0) as UNSIGNED) as total_duration_in_seconds, count(pc.id) as number_of_clips from playlists p left join playlist_clips pc on p.id = pc.playlist_id where p.title like "Test1" group by id ) as p_clips inner join ( select * from playlists p left join playlist_tags pt on p.id = pt.playlist_id where pt.tag_id in (1, 2) group by id having count(*) = 2 ) as p_tags on p_clips.id = p_tags.id
Whilst, from my testing I’ve found this to work, it doesn’t look particularly elegant, and I also assume it’s not terribly efficient performance-wise. (I’ve removed irrelevant parameters from the code for this example, such as
What would be a cleaner way of approaching this, or at the least, a more optimized approach?
id title 260 Title1
EDIT: I apologize for my initial confusing post, I’ve tried to clean up my tables and the information they contain.
Assuming you want summary information from both the clips and the playlist:
select p.* from playlists p join (select pt.playlist_id from playlist_tags pt where pt.tag_id in (1, 2) group by id having count(*) = 2 ) pt on p.id = pt.playlist_id join (select pc.playlist_id, group_concat(pc.clip_id) as clips from playlist_clips pc group by pc.playlist_id ) pc on p.id = pc.playlist_id where p.title like 'Test1';
Note: This puts all the clip information into one column.