How to use JOIN on a non-existent record

Transact-SQL
1

Suppose I have a table called "HEADER", which looks like this:

OrderID, Employee
1 102
2 335
etc.

And I have a table called "SALES", looking like this:
1, Month, Qty
1, Jan, 1
1, Feb, 2
1, Mar, 1
1, Apr, 3
1, May, 4
1, Jun, 5
1, Jul, 1
1, Aug, 1
1, Sep, 1
1, Nov, 1
1, Dec, 10

Notice the entry for Oct doesn't exist at all.

2, Jan, 1
2, Feb, 2
2, Mar, 1
2, Apr, 3
2, May, 4
2, Jun, 5
2, Jul, 1
2, Aug, 1
2, Sep, 1
2. Oct, NULL
2, Nov, 1
2, Dec, 10

Notice the entry for Oct is present as NULL.

How can I write a select that will display the OrderID if either Oct is non-existent (see OrderID 1),
or the Oct record is NULL (see OrderID 2).

Unfortunately I can't redesign anything here. It's 3rd party software.

2

For just specifically Oct, you can do this:

SELECT H.OrderID
FROM Header H
WHERE 
    EXISTS(SELECT 1 FROM Sales S WHERE H.OrderID = S.OrderID AND 
        Month = 'Oct' AND Qty IS NULL) OR
    NOT EXISTS(SELECT 1 FROM Sales S WHERE H.OrderID = S.OrderID AND 
        Month = 'Oct')
3

Scott - just brilliant! THank you so very much. I came close, but I didn't think of using "NOT EXISTS".
I was so focused on "IF EXISTS".

Again, thank you!
Corey

4

Great, glad it helped!