CODEWITHSUNDEEP
sql
user3267755
user3267755

Reputation: 1070

SQL statement confusion

I have a table that will have data like this

PRODUCTID | GENREID
1         | 5
1         | 6
1         | 8
2         | 5
2         | 10
3         | 6

And I'm trying to write a statement to where if I provide the numbers 5, 6 and 8, it will only return to me ProductID '1'. My initial thought was to write something like this, but I'm starting to confuse myself as I'm not a SQL ninja :

select 
    p1.productid 
from 
    dbo.ProductGenre p1 
inner join
    dbo.ProductGenre p2 on p2.GenreID = p1.GenreID 
where 
    p1.GenreID = 5

Upvotes: 0

Views: 45

Answers (1)

juergen d
juergen d

Reputation: 204746

SELECT productid
FROM   dbo.productgenre
WHERE  genreid IN ( 5, 6, 8 )
GROUP  BY productid
HAVING Count(DISTINCT genreid) = 3

Upvotes: 3

Related Questions