CODEWITHSUNDEEP
sql-server
Raju G
Raju G

Reputation: 97

SQL Query to get data based on multiple filters

I have following Product table and ProductTag tables -

ID  | Product
-------------- 
1   | Product_A
2   | Product_B
3   | Product_C

TagID   |   ProductID
----------------------
1       |      2
1       |      3
2       |      1
2       |      2
2       |      3
3       |      1
3       |      2

Now I need a SQL query that return all products list which are having both Tag 1 and 2. Result should be as given below -

ProductID   |    Product   
------------------------
2           |      Product_B
3           |     Product_C

Please suggest how can i write a MS SQL query for this.

Upvotes: 1

Views: 51

Answers (1)

John Woo
John Woo

Reputation: 263933

SELECT  p.ID, p.Product
FROM    Product p
        INNER JOIN ProductTag pt
            ON p.ID = pt.ProductID
WHERE   pt.TagID IN (1, 2)           -- <== Tags you want to find
GROUP   BY p.ID, o.Product
HAVING  COUNT(*) = 2                 -- <== tag count on WHERE clause

however, if TagID is not unique on every Product, you need to count only the distinct product.

HAVING  COUNT(DISTINCT pt.TagID) = 2

Upvotes: 1

Related Questions