CODEWITHSUNDEEP
sqlnetezza
Matthew Clark
Matthew Clark

Reputation: 13

Find missing records from sequence

I need to find the missing records in a table. Table 1:

ID|Num
X|1
X|2
X|3
X|4
Y|1
Y|3
Y|5

Table 2:

Num
1
2
3
4
5

I need to return:

ID|Num

X|5

Y|2

Y|4

I've found other solutions that would give me 5,2,4 but I need the ID associated with the missing record as well.

Upvotes: 1

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

If you want the missing numbers, use a cross join to generate all numbers and then filter out the ones that exist:

select i.id, t2.num
from (select distinct id from t1) i cross join
     table2 t2 left join
     t1
     on t1.id = i.id and t1.num = t2.num
where t1.id is null;

Upvotes: 2

Related Questions