SQL Query to get data based on multiple filters

Question

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.

John Woo · Accepted Answer · 2014-10-30 04:03:01Z

1

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

More on: SQL of Relational Division

edited Oct 30, 2014 at 4:03

answered Oct 30, 2014 at 3:54

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query to get data based on multiple filters

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related