February 1, 2021 at 12:53 am
Since you always want to list all recording artists, I'd start at that table. I would think it's possible that info on an album by a new artist might not yet be in the album table, so doing an inner join to that table would drop any such new artist. Similarly, although much less likely, is if you ever search for a genre that no artist matches, and thus is not even in your system's genre table -- say "Paraguayan polka" -- you'd still want to return all artists, according to the rules you've stated, but all artists should return a zero count.
Another point is that the artist table has far fewer rows than the song table. In general, it's much better in SQL to limit total rows that you have to process as soon as possible in the process. Note that joining to the genre table first also reduces the number of matches in the album table.
Finally, I'd do the count of song, just to improve understanding of anyone reading the query, since that is what we are officially counting: song not genre.
SELECT ra.artist_name, COUNT(s.song_id) AS rock_song_count
FROM recording_artist ra
LEFT OUTER JOIN musical_genre mg ON mg.musical_genre = 'Rock'
LEFT OUTER JOIN album a ON a.recording_artist_id = ra.recording_artist_id AND
a.musical_genre_id = mg.musical_genre_id
LEFT OUTER JOIN song s ON s.album_id = a.album_id
ORDER BY rock_song_count, artist_name
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply