CODEWITHSUNDEEP
mysqlsqljoinrelational-database
Srinivas B
Srinivas B

Reputation: 7

MySQL query left join issue

I have two tables:

product table
  product_id  product_name
       1         sample1
       2         sample2 
       3         sample3  

product_child table
  pc_id  product_id  product_size
    1        1          5
    2        1          6
    3        1          7
    4        2          5
    5        2          8
    6        2          7
    7        3          8
    8        3          6
    9        3          9

My question is:
I want to get all products where product_size (5 or 9) and 6.
It means I want result like:

 product_id  product_name
       1         sample1
       3         sample3

I need query like : 

SELECT p.*
FROM   products p
       LEFT JOIN product_child pc
              ON pc.product_id = p.product_id
WHERE ( pc.product_size = 5 OR pc.product_size = 9)
       AND pc.product_size = 6

but it's not working.

Upvotes: 1

Views: 50

Answers (3)

Jens
Jens

Reputation: 69480

Try this query:

SELECT p.*
FROM   products p
       JOIN product_child pc1 ON pc1.product_id = p.product_id and pc1.product_size  in (5,9)
       JOIN product_child pc2 ON pc2.product_id = p.product_id and pc2.product_size  = 6

Upvotes: 1

rtruszk
rtruszk

Reputation: 3922

If I understand correctly you need all products which have both sizes.
So you have different products and every product appears in different sizes. And you want only these products which appear in both (5 or 9) and 6 sizes at the same time.
I think you need something like this:

SELECT * from products where product_id IN
( SELECT product_id from product_child where product_size = 5 or product_size = 9
 INTERSECT
 SELECT product_id from product_child where product_size = 6
)

In above query I did intersection.
I selected one set (product of size 5 or 9) and second set (product of size 6). Intersection means finding products which appear in both sets.

Upvotes: 1

Huy.Vu
Huy.Vu

Reputation: 259

WHERE  pc.product_size = 5
       AND pc.product_size = 6

replace AND with OR and it will be right.

Upvotes: 0

Related Questions