Luis Dalmolin Luis Dalmolin - 17 days ago 5
SQL Question

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.

Answer

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.