CODEWITHSUNDEEP
sqlpostgresql
joe
joe

Reputation: 335

Combining results from 3 tables

I'm using Postgres 10, with the following structure and data:

create table table1(
  id serial primary key
);

create table table2(
  id serial primary key
);

create table table3(
  id serial primary key,
  table1_id int,
  table2_id int
);

insert into table1 (id) values (1), (2), (3);
insert into table2 (id) values (1), (2);
insert into table3 (id, table1_id, table2_id) values (1, 1, 1), (2, 1, 2), (3, 2, 1);

I want all combinations of table1 and table2 that don't have an entry in table3, essentially this sort of result:

+-----------+-----------+-----------+
| table1.id | table2.id | table3.id |
+-----------+-----------+-----------+
|         1 |         1 | 1         |
|         2 |         1 | 3         |
|         3 |         1 | null      |
|         1 |         2 | 2         |
|         2 |         2 | null      |
|         3 |         2 | null      |
+-----------+-----------+-----------+

Notice how the results have all the possible combinations from table1 and table2, with the corresponding id from table3.

Eventually, I would like to get the query to only return rows where table3.id is NULL:

+-----------+-----------+-----------+
| table1.id | table2.id | table3.id |
+-----------+-----------+-----------+
|         3 |         1 | null      |
|         2 |         2 | null      |
|         3 |         2 | null      |
+-----------+-----------+-----------+

I am unsure on how to even approach this query: join or inner selects or maybe even excludes?

Upvotes: 1

Views: 50

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You can try to use CROSS JOIN (cartesian product) on table1 and table2 then table3 LEFT JOIN base on the CROSS JOIN result set.

Schema (PostgreSQL v9.6)

create table table1(
  id serial primary key
);

create table table2(
  id serial primary key
);

create table table3(
  id serial primary key,
  table1_id int,
  table2_id int
);

insert into table1 (id) values (1), (2), (3);
insert into table2 (id) values (1), (2);
insert into table3 (id, table1_id, table2_id) values (1, 1, 1), (2, 1, 2), (3, 2, 1);

Query #1

SELECT t1.id,t2.id,t3.id 
FROM table1 t1 CROSS JOIN table2 t2
LEFT JOIN table3 t3 
on t3.table1_id = t1.id and t3.table2_id = t2.id
where t3.id is null;

Result

t1id  t2id  t3id
2      2    null
3      1    null
3      2    null

View on DB Fiddle

Upvotes: 1

Related Questions