1

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.

3

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
1

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.

  • 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). – asyncerror Jan 10 at 21:07
  • @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…) – Abecee Jan 10 at 21:12
  • 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. – asyncerror Jan 10 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.