SQL View to find missing values, should be simple

Question

I have two tables with a common id, table1 has a task numbers column and table2 has documents column each task can have multiple documents. I'm trying to find all task numbers that don't have a specific document

Fake data:

SELECT * FROM table1
id  tasknumber
1   3210-012
2   3210-022
3   3210-032

SELECT * FROM table2
id  document
1   revision1
1   SB
1   Ref
2   revision1
2   Ref
3   revision1
3   SB

But how would I find tasknumbers which don't have a document named SB?

LEFT JOIN and null check (on right relation) or JOIN .. GROUP BY .. HAVING COUNT are two methods. Could also use an EXCEPT, etc. — user2864740, Oct 21 '13 at 18:55

score 3 · Accepted Answer · edited May 23 '17 at 11:49

3

SELECT t1.tasknumber
FROM   table1 t1
LEFT   JOIN table2 t2 ON t2.id = t1.id AND t2.document = 'SB'
WHERE  t2.id IS NULL;

There are basically four techniques:

Select rows which are not present in other table

edited May 23 '17 at 11:49

Community

1
1

answered Oct 21 '13 at 18:55

Erwin Brandstetter

605,456
145
1,078
1,228

score 1 · Answer 2 · answered Oct 21 '13 at 18:55

1

select t1.tasknumber from table1 t1
where not exists 
     (select 1 from table2 t2 where t1.id = t2.id and t2.document = 'SB')

answered Oct 21 '13 at 18:55

Nir Alfasi

53,191
11
86
129

Joe · Answer 3 · 2013-10-21T19:06:26.743

1

select
    tasknumber
from 
    table1
where
    not exists (select 1
                  from table2
                 where table1.id = table2.id
                       and table2.document = 'SB');

or

select
    tasknumber
from 
    table1
where
    id not in (select id
                 from table2 
                where document = 'SB');

edited Oct 21 '13 at 19:06

answered Oct 21 '13 at 18:56

Joe

6,767
1
16
29

Closing parentheses missing. – ypercubeᵀᴹ Oct 21 '13 at 19:05

Amir Keshavarz · Answer 4 · 2013-10-22T14:55:44.710

0

Use this:

SELECT TASKNUMBER 
FROM TABLE1
WHERE ID NOT IN (SELECT DISTINCT ID FROM TABLE 2 where document = 'SB')

edited Oct 22 '13 at 14:55

answered Oct 21 '13 at 18:55

Amir Keshavarz

3,050
1
19
26

1

that 'and' at the end is unnecessary, but thanks that works great – mrplow Oct 21 '13 at 20:03
@Amireza is it so hard to edit the answer so it can actually run without errors? – ypercubeᵀᴹ Oct 22 '13 at 14:52
Sorry thanks I eddited it – Amir Keshavarz Oct 22 '13 at 14:56
1

@mrplow: You picked the least favorable solution. `NOT IN` is generally slowest and fails if `NULL` is involved. – Erwin Brandstetter Oct 22 '13 at 21:25

SQL View to find missing values, should be simple

4 Answers4