CODEWITHSUNDEEP
sqlsql-server
lucky.expert
lucky.expert

Reputation: 793

Find missing records in many-to-many junction table

I have a many-to-many junction table that joins 2 tables:

table1 

table1_id 
-----------
1
2
3

table2

table2_id
------------
A
B
C

join_table

table1_id   |   table2_id
------------|--------------
1           |  A
1           |  B
1           |  C
2           |  A
2           |  C
3           |  B

How can I write a single query to find out what records are missing from the join table, since I want all combinations accounted for. Basically, I want a query that returns these missing records:

table1_id   |   table2_id
---------------------------
2           |  B
3           |  A
3           |  C

I feel like this should be simple but I haven't figured it out.

Upvotes: 1

Views: 244

Answers (1)

jarlh
jarlh

Reputation: 44796

Do a CROSS JOIN to get all possible combinations. Then use EXCEPT to remove the existing combinations.

select t1.table1_id, t2.table2_id
from t1 cross join t2
except
select table1_id, table2_id from join_table

Upvotes: 8

Related Questions