Search code examples
sqlrelational-division

SQL Join IN Query with AND?

I have the following tables:

Option
-------
id - int
name - varchar

Product
---------
id - int
name -varchar

ProductOptions
------------------
id - int
product_id - int
option_id - int

If I have a list of option ids, how can I retrieve all Products that have all the options with the list of ids that I have? I know that SQL "IN" will use an "OR" i need an "AND". Thank you!

Solution

  • If the ids are not repeated, you can retrieve the ids of the options you need and count how many they are. Then, you just

    SELECT product_id FROM ProductOptions
    WHERE option_id IN ( OPTIONS )
    GROUP BY product_id
    HAVING COUNT(product_id) = NEEDED;

    Without the GROUP BY, if you had five option ids, and product 27 had fifteen options among which there were those five, you'd get five rows with the same product_id. The GROUP BY joins those rows. Since you want ALL options, and options have all different IDs, asking "rows with all of them" is equivalent to asking "rows with as many options as the desired option set size".

    Plus, you run the big query on ProductOptions only, which should be really fast.