i have two tables: product and productVariation: product variation table contains multiple entries for the same product but with different attributes.
example: product: id name 1 top 2 bottom
id | name |
---|---|
1 | top |
2 | bottom |
productVariation table:
id | product_id | color_id | size_id |
---|---|---|---|
1 | 1 | 4 | 7 |
2 | 1 | 5 | 7 |
3 | 1 | 8 | 7 |
4 | 1 | 7 | 10 |
so for the above tables, when I am querying that data for the products list to display, let's say the user wants data for size_id 7 then we have 3 possibilities (3 different colors) but on the products list page, I just want to show one entry for this product. My question is how can I just get one entry based on the product_id from the product variation table. I already wrote a query that is filtering the data based on color and size but I get multiple entries for the same product, I just want one of these rows.
Thank you! :)
CodePudding user response:
I'm guessing row_number could be useful here.
If your version of MySql or MariaDB supports it.
SELECT product_id, product_name, color_id, size_id
FROM
(
SELECT
pvar.product_id
, product.name AS product_name
, pvar.color_id
, pvar.size_id
, row_number() over (partition by pvar.product_id order by pvar.id DESC) AS rn
FROM product AS product
JOIN productVariation AS pvar
ON pvar.product_id = product.id
WHERE pvar.product_id IN (1)
AND pvar.size_id = 7
) q
WHERE rn = 1
Or maybe just LIMIT
the resultset.
SELECT
pvar.product_id
, product.name AS product_name
, pvar.color_id
, pvar.size_id
FROM product AS product
JOIN productVariation AS pvar
ON pvar.product_id = product.id
WHERE pvar.product_id IN (1)
AND pvar.size_id = 7
ORDER BY pvar.product_id, pvar.id DESC
LIMIT 1
CodePudding user response:
You could aggregate your multiple colors and group by the size, taking the min or max as the one to pick. Doing so in a lateral join would look like
select *
from products p
left join lateral(
select size_id, max(color_id) color_id
from productvariations v
where v.product_id = p.id
group by size_id
)pv on true
where pv.size_id = 7;