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

Question

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 1

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

SELECT orders.id,
       addresses.name,
       array_agg(DISTINCT products.sku )
FROM orders 
LEFT JOIN addresses
ON orders.address_id = addresses.id
LEFT JOIN line_items
ON line_items.order_id = orders.id
LEFT JOIN  products
ON products.id = line_items.product_id
GROUP BY orders.id,addresses.name

Answer 2

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