I am having issues with this simple scenario. I'm probably missing some basic concept...
I have 2 tables, the first of "things" and the second of orders. I've created a sqlfiddle: http://sqlfiddle.com/#!17/e9d19/6/0
select t1.*, t2.* from things t1 left join things_orders t2 on t1.id = t2.thing_id
I want to have the results show a row for each item and person. In my example, Adam ordered an apple, 2 bananas and 3 cherries. Ben ordered no apples (no row in the database), 2 bananas and 3 cherries. I want the result to have 6 rows (simplified output):
Apple Adam 1 Banana Adam 2 Cherry Adam 3 Apple Ben null <-- wanted row, but not showing Banana Ben 2 Cherry Ben 3
I didn't think I needed to have a row in the second table with a null value but maybe I do.
This is the DDL which is also in the sqlfiddle:
CREATE TABLE things ( id smallint NOT NULL, name text COLLATE pg_catalog."default", CONSTRAINT things_pkey PRIMARY KEY (id) ); CREATE TABLE things_orders ( person text COLLATE pg_catalog."default" NOT NULL, thing_id smallint NOT NULL, qty integer, CONSTRAINT things_orders_pk PRIMARY KEY (person, thing_id) ); INSERT INTO things VALUES (1, 'Apple') , (2, 'Banana') , (3, 'Cherry') ; INSERT INTO things_orders VALUES ('Adam', 1, 1) , ('Adam', 2, 2) , ('Adam', 3, 3) , ('Ben', 2, 2) , ('Ben', 3, 3) ;