CODEWITHSUNDEEP
c#sqlkeycombinations
Saman
Saman

Reputation: 19

How To Check If A Combination Of Foreign Keys Exists Multiple Times

So I have a database where there's the tables: Song, Artist and Producer. I want to check if an artist has produced more than 1 song at the same producer. The Song-Table already has the foreign keys of the artistID and producerID but how do I see in the song table if there's multiple combinations from those same ID's which would mean that the artist has producer more than 1 song at the same producer. This is what I have now, but I've been really stuck after this: 

query = "select Name from Artist where 1 < (Select count(*) from Song where....";

Upvotes: 0

Views: 553

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

This sounds like a basic aggregation:

select artistId, producerId, count(*) as numsongs
from song
group by artistId, producerId
having count(*) > 1;

You can join back to Artist and/or Producer if you want names instead of ids.

Upvotes: 1

Related Questions