CODEWITHSUNDEEP
mysqlsqlmany-to-many
Szymon Wygnański
Szymon Wygnański

Reputation: 11064

find items whitch has 2 associations (together) using mysql

I've got the database with 3 tables: 

product (id, name, price, ...)
product_option (id, product_id, option_id)
option (id, value)

products have many options

For example I need to find a product with option A and option B and not a product with just an option A.

I'm using somethink like this: 

select * from product
left join product_option on product_option.product_id = product_id

where product_option.option_id in (1,2,3) 
and product_option.option_id in (4)
group by product.id

Of course the result is always empty. I need those products with option_id (1 or 2 or 3) and 4

How can I do that?

Upvotes: 0

Views: 62

Answers (5)

ajreal
ajreal

Reputation: 47331

select *
from product as p
inner join (product_option as po, product_option as po2)
on p.product_id=po.product_id and p.product_id=po2.product_id
where po.option_id=4 and po2.option_id in(1,2,3)
group by p.product_id;

Upvotes: 1

drpip
drpip

Reputation: 73

I haven't set up your tables on my own DB, but I've done something similar recently. Try this:

SELECT * FROM product, product_option AS PO1, product_option AS PO2
WHERE product.id = PO1.product_id
  AND product.id = PO2.product_id
  AND PO1.option_id IN (1, 2, 3)
  AND PO2.option_id = 4;

Upvotes: 1

Marcelo Cantos
Marcelo Cantos

Reputation: 186118

SELECT *
  FROM product p
 WHERE EXISTS (
        SELECT *
          FROM product_option
         WHERE product_id = p.id
           AND option_id in (1, 2, 3)
       )
   AND EXISTS (
        SELECT *
          FROM product_option
         WHERE product_id = p.id
           AND option_id = 4
       )

Upvotes: 1

sll
sll

Reputation: 62564

WHERE
(option_id IN (1,2) AND option_id NOT IN (3,4)) 
OR   
(option_id IN (4) AND option_id NOT IN (1,2,3))

Not sure I got combination pairs right so hope you got the idea

Upvotes: 1

Puggan Se
Puggan Se

Reputation: 5846

as you want two difrent matches, you need to join the product_option twise

select * from product
left join product_option AS po1 on product_option.product_id = product_id
left join product_option AS po2 on product_option.product_id = product_id

where po1.option_id in (1,2,3) 
and po2.option_id in (4)
group by product.id

Upvotes: 1

Related Questions