SQL Server Query – Select Rows with Multiple Required Filters

sql serversql-server-2008

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.

Best Answer

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
Related Question