CODEWITHSUNDEEP
mysqlsql
Luis Dalmolin
Luis Dalmolin

Reputation: 3486

MySQL - Find result that have children values

I have this table structure:

product_skus table

| id |
| 1  | 
...

product_sku_values table

| product_sku_id | value_id |
| 1              | 1        |
| 1              | 2        |
| 1              | 3        |
...

I need the query to find the product_sku_id, having the three values ID's (1, 2, and 3).

I'm trying with this query:

select product_sku_id from product_sku_values
    where product_sku_values.value_id = 1
    or    product_sku_values.value_id = 2
    or    product_sku_values.value_id = 3
    group by product_sku_id
    having product_sku_id = 1

How can I do that? I'm trying lot of possibilities but no one give me the ID that I need. Can somebody help me?

Thanks.

Upvotes: 0

Views: 57

Answers (2)

Darshan Mehta
Darshan Mehta

Reputation: 30809

You can simply use group by clause to get all the possible values, e.g.:

select product_sku_id, group_concat(value_id) 
from product_sku_values
group by product_sku_id;

If you are only interested in value_id 1,2 and 3 then you can add one where clause, e.g:

select product_sku_id, group_concat(value_id)
from product_sku_values
where value_id in (1,2,3)
group by product_sku_id;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

This is a canonical method:

select psv.product_sku_id
from product_sku_values psv
where psv.value_id in (1, 2, 3)
group by psv.product_sku_id
having count(distinct psv.value_id) = 3;

If you know that product_sku_values have no duplicates, then use count(*) in the having clause.

Upvotes: 2

Related Questions