clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1044295 fiddles created (9416 in the last week).

CREATE TABLE item (id int PRIMARY KEY); INSERT INTO item VALUES (1), (2), (3), (4); CREATE TABLE item_stock ( item_id int NOT NULL , shop_id int NOT NULL , stock int , cost numeric(19,3) , PRIMARY KEY (shop_id, item_id) INCLUDE (stock) ); INSERT INTO item_stock VALUES (1, 1, 2, 10), (1, 2, 0, 9), (2, 2, 0, 9), (3, 1, 3, 22);
4 rows affected
4 rows affected
 hidden batch(es)


-- original by plaes, improved SELECT b.id, array_agg(b.stock) AS stock FROM ( SELECT i.id, COALESCE(i_s.stock, 0) AS stock FROM item i CROSS JOIN unnest('{1,2}'::int[]) n LEFT JOIN item_stock i_s ON i.id = i_s.item_id AND n.n = i_s.shop_id ORDER BY i.id, n.n ) b GROUP BY b.id;
id stock
1 {2,0}
2 {0,0}
3 {3,0}
4 {0,0}
 hidden batch(es)


-- with LEFT JOIN LATERAL & ARRAY constructor SELECT i.id, s.stock FROM item i CROSS JOIN LATERAL ( SELECT ARRAY( SELECT COALESCE(i_s.stock, 0) FROM unnest('{1,2}'::int[]) n LEFT JOIN item_stock i_s ON i_s.shop_id = n.n AND i_s.item_id = i.id ORDER BY n.n ) AS stock ) s;
id stock
1 {2,0}
2 {0,0}
3 {3,0}
4 {0,0}
 hidden batch(es)


-- LEFT JOIN & crosstab() & COALESCE SELECT i.id, COALESCE(stock, '{0,0}') AS stock FROM item i LEFT JOIN ( SELECT id, ARRAY[COALESCE(shop1, 0), COALESCE(shop2, 0)] AS stock FROM crosstab( $$SELECT item_id, shop_id, stock FROM item_stock WHERE shop_id = ANY ('{1,2}'::int[]) ORDER BY 1,2$$ , $$SELECT unnest('{1,2}'::int[])$$ ) AS ct (id int, shop1 int, shop2 int) ) i_s USING (id);
id stock
1 {2,0}
2 {0,0}
3 {3,0}
4 {0,0}
 hidden batch(es)