CODEWITHSUNDEEP
sql-server
comidos
comidos

Reputation: 123

Finding Products with multiple categories

I'm having the Tables "Products", "Categories" and the Relationship-Table for the ForeignKeys "ProductsToCategories" in the classic Normalisation-Way.

In my Application you can choose Category with Dropdownlist, but I want to switch to RadioButtons where the User can choose more than one category.

How can i realise the Query to find only Products where a few Entities match in the Relationship-Table?

Pseudo-Code: select * from Products inner join ProductsToCategories ... where Category-ID = 35 AND Category-ID = 36 AND Category-ID = 9

Upvotes: 0

Views: 787

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

One simple option uses aggregation:

SELECT
    p.id,
    p.name
FROM Products p
INNER JOIN ProductsToCategories pc
    ON p.id = pc.product_id
WHERE
    pc.category_id IN (9, 35, 36)
GROUP BY
    p.id,
    p.name
HAVING
    COUNT(DISTINCT pc.category_id) = 3;

The basic idea here is to aggregate by each product, first removing all records except those belonging to the three categories of interest. Then, we assert that what remains are three distinct categories, implying that the product is a match.

Upvotes: 2

Related Questions