I have 4 tables called orders, order_details, products, and storages. Every products can have multiple thumbnail image that saved on storages table.
I want to return specific orders by id which return multiple row of order_details where every order_details have only one product. In every product, I want to get only one thumbnail image from storages table.
Here's what comes in my mind if I want to get a row where order_id = 1,
SELECT *
FROM orders o
JOIN order_details od ON o.id = od.order_id
JOIN products p ON p.id = od.product_id
JOIN storages s ON s.product_id = p.id --> i haven't figured out how to return only one image for every product in order_details
WHERE o.id = 1
Can somebody give me a help, I've tried to figured out this for days but still not getting it right :(
Thank you in advance.
I haven't figured out how to return only one image for every product in order_details
In Postgres, I would recommend distinct on:
select distinct on (o.id, od.product_id) *
from orders o
join order_details od on o.id = od.order_id
join products p on p.id = od.product_id
join storages s on s.product_id = p.id
order by o.id, od.product_id, s.id
This guarantees just one rows per order and product, with the storage that has the smallest id. You can filter on a given order id with a where clause, if you like.
Or maybe you want to use the primary key of the order details instead of the product (this allows twice the same product in two different order details of the same order).
select distinct on (o.id, od.id) *
from orders o
join order_details od on o.id = od.order_id
join products p on p.id = od.product_id
join storages s on s.product_id = p.id
order by o.id, od.id, s.id