Select rows that have multiple required filters

Question

How can I select artists who have punk and rock in their musical genres? I need artists with both genres.

My tables

A example:

SELECT * FROM artist_genre WHERE genre_id IN (1,2)

In this way I recover all that belong to punk, or rock. But I only need artists who own both. Is there a easy way for this?

If a change in the structure of the databases is required, I'm up to do.

Answer 1

This is referred to as the intersection of sets. One way to perform the query is the use the INTERSECT operator:

SELECT ag.artist_id 
FROM dbo.artists_genres ag
WHERE ag.genre_id = 1
INTERSECT
SELECT ag.artist_id 
FROM dbo.artists_genres ag
WHERE ag.genre_id = 2

INTERSECT returns distinct rows that are output by both the left and right inputs. The first part of the query retrieves the artist_id where the genre_id is 1, the second part of the query retrieves the artist_id where the genre_id is 2, and the INTERSECT operator then compares both lists, returning only rows that match precisely, eliminating duplicates.

This is very similar to using an INNER JOIN against two common-table-expressions, commonly referred to as CTEs, as in:

;WITH g1 AS
(
 SELECT *
 FROM dbo.artists_genres ag1
 WHERE ag1.genre_id = 1
), g2 AS
(
 SELECT *
 FROM dbo.artists_genres ag1
 WHERE ag1.genre_id = 2
)
SELECT g1.artist_id
FROM g1
 INNER JOIN g2 ON g1.artist_id = g2.artist_id;

If you don't like common-table-expressions (CTEs), this looks like:

SELECT g1.artist_id
FROM
 (
 SELECT *
 FROM dbo.artists_genres ag1
 WHERE ag1.genre_id = 1
 ) g1
 INNER JOIN
 (
 SELECT *
 FROM dbo.artists_genres ag1
 WHERE ag1.genre_id = 2
 ) g2 ON g1.artist_id = g2.artist_id

Answer 2

With SQL Server 2017, you need to access the table only once:

SELECT
 artist_id FROM
(
 SELECT
 artist_id
 , STRING_AGG(genre_id, ',') WITHIN GROUP (ORDER BY genre_id) genre_combination
 FROM ArtistGenre
 WHERE genre_id in (1, 2)
 GROUP BY artist_id
)
 WHERE genre_combination = '1,2'
;

This way you get all artists, which have at least the wanted genres in their repertoire. If you want only those, who have exactly that combination, drop the first of the two WHERE clauses.

Please comment, if and as this requires adjustment / further detail.

• asyncerror: Looks like a nice solution too! Unfortunately I could not run it, I think my SQL Server does not support the STRING_AGG function, I got the error: 'STRING_AGG' is not a recognized built-in function name. My SQL is Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64) Aug 19 2014 12:21:34 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor).
• Abecee: @asyncerror You tagged your question with SQL Server 2008. STRING_AGG is available with SQL Server 2017. But once you upgrade... (And other people seeing the question might already be on the more recent version...)
• asyncerror: Oh, got it! I read it 2007, instead 2017. I can't upvote because I don't have enough reputation, but I greatly appreciated your response.