There is a table product (id, name) . There is a table product_option (id, product_id, option_id) A product may have several options, or it may not be at all.

How to get a list of products that have options (option_id) in one request, say: 1, 14, 25?

    2 answers 2

    SELECT p.id, p.name FROM product AS p JOIN product_option AS o ON p.id = o.product_id AND o.option_id in(1, 14, 25) GROUP BY p.id, p.name HAVING count(1)=3

    3 in the condition HAVING is the number of conditions that must match.

    Will work provided that the product can not have two identical options. Your database structure does not guarantee this, because Uniqueness is certain only in the id field of the product_option table. I recommend removing the id field and the primary key altogether as a composite key from the two remaining columns, primary key (option_id, product_id) .

      You can do the following:

       SELECT p.id, p.name FROM product AS p JOIN product_option AS o1 ON p.id = o1.product_id AND o1.option_id = 1 JOIN product_option AS o2 ON p.id = o2.product_id AND o2.option_id = 14 JOIN product_option AS o3 ON p.id = o3.product_id AND o3.option_id = 25
      • option_id IN (1, 14, 25) says that all options containing 1 OR 14 OR 25. will do. I also need the product to have at least 1 AND 14 and 25 options together. For example, if there is only 1 and 25, but not 14 - by. - GroZa
      • @GroZa corrected the answer, it does not work out very elegantly, but so far has not come up with a better answer. The task is interesting, thank you. - cheops