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?

Answer 1

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