Table Name : tblUserProduct
How to Get List of Users Who has Speicific Products
Eg: Get List of Users Who have all ProductID’s P1 & P3.
In below example U1 & U4 has both ProductID’s p1 & p3
| UserID | ProductID |
|---|---|
| U1 | P1 |
| U1 | P3 |
| U2 | P3 |
| U1 | P2 |
| U4 | P4 |
| U4 | P1 |
| U4 | P3 |
| U5 | P5 |
>Solution :
use this :
SELECT USERID FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY USERID) AS RNO,USERID
FROM TBLUSERPRODUCT WHERE PRODUCTID IN ('P1','P3')
) A WHERE RNO =2
