esharp esharp - 6 months ago 20
SQL Question

SQL aggregate query with one-to-many relationship with postgres

I'm trying to aggregate a list of product skus with a query that relates through a line_items table. I've abstracted a simple example of my use case:

my expected result would look like this:

id name skus
1 mike bar sku1,sku2,sku3
2 bort baz sku4


given a schema and data like:

products

id sku
1 sku1
2 sku2
3 sku3
4 sku4


line_items

id order_id product_id
1 1 1
2 1 2
3 1 3
4 2 4


addresses

id name
1 'bill foo'
2 'mike bar'
3 'bort baz'


orders

id address_id total
1 2 66
2 3 99


here's a working query, but it's not correct, i'm getting ALL products for each order. my
WHERE
should be using
orders.id


http://sqlfiddle.com/#!15/70cd7/3/0

however, i can't seem to use
orders.id
? i'm guessing i need to use a
JOIN
or
LEFT JOIN
or somehow change the order of things in my query...

http://sqlfiddle.com/#!15/70cd7/4

Answer

You can use a correlated subquery with a JOIN to get the list of skus for each order

SELECT
    o.id,
    a.name,
    (SELECT array_to_string(array_agg(sku), ',') AS Skus
     FROM products p
     INNER JOIN line_items li
        ON li.product_id = p.id
     WHERE li.order_id = o.id
    ) AS Skus
FROM orders o
INNER JOIN addresses a
  ON a.id = o.address_id

ONLINE DEMO