CODEWITHSUNDEEP
sqlsql-server
Sean Anderson
Sean Anderson

Reputation: 29301

Finding parents by matching on children FKs

I have a database with two tables, Playlists and PlaylistItems. PlaylistItems have a FK back to their Playlist. The tables look like:

PlaylistItems: Id (PK) | Title | PlaylistId (FK)
Playlists: Id (PK) | Title

I've got a couple of queries which find me PlaylistItems by title:

Select * from PlaylistItems where Title LIKE '%Geographer - Kites%'
Select * from PlaylistItems where Title LIKE '%Sam Kang%'

Each of these queries yields many results, but I believe that there should be only a minimal number of Playlists which contain both results. That is, I'm looking for all Playlists which contain both '%Geographer - Kites%' and '%Sam Kang%' as children.

I'm considering doing a JOIN on each of my Select statements, then performing a COUNT on the PlaylistId and return results which have a COUNT > 1. However, I think that solution will also show playlists which contains 2x '%Geographer - Kites%' and 0x '%Sam Kang%'.

Is there a more clever way to achieve this?

Upvotes: 0

Views: 39

Answers (2)

Ken
Ken

Reputation: 215

If you're not allergic to subqueries, you can try something like

select distinct PlaylistId from PlaylistItems where 
    Title LIKE '%Geographer - Kites%' and
    PlaylistId in (select distinct PlaylistId from PlaylistItems where Title LIKE '%Sam Kang%')

I don't have access to SQL Server, so I haven't been able to verify it directly.

Upvotes: 1

Jithin Shaji
Jithin Shaji

Reputation: 6073

Sean, will this work, or can we work from here?

SELECT  * FROM Playlists WHERE ID IN(
SELECT PlaylistId FROM PlaylistItems where Title LIKE '%Geographer - Kites%'
INTERSECT
SELECT PlaylistId FROM PlaylistItems where Title LIKE '%Sam Kang%')

Upvotes: 1

Related Questions