I have a table of invoices and one of contracts that invoices are linked to. However, not every invoice is linked to a contract. I want to return all invoices linked to contracts which are within a timeframe (Starting before 2021-03-01), and also the invoices with no contracts linked at all. I believe this is something like: contract IS null OR CASE WHEN contract IS NOT NULL THEN (condition on timestamp), but I don't know how to write it. Note that the actual conditioning will be done on multiple columns, so I am looking for the general form of multiple sub-conditions under the conditions in the WHERE clause.
Example:
Invoice Table
| InvoiceID | ContractID |
|---|---|
| 1 | NULL |
| 2 | 1 |
| 3 | NULL |
| 4 | 2 |
| 5 | 3 |
| 6 | 4 |
Contract Table
| ContractID | Contract Start Timestamp |
|---|---|
| 1 | 2021-01-01 00:00:00 |
| 2 | 2021-02-01 00:00:00 |
| 3 | 2021-03-02 00:00:00 |
| 4 | 2021-05-01 00:00:00 |
Desired Result
| InvoiceID | ContractID |
|---|---|
| 1 | NULL |
| 2 | 1 |
| 3 | NULL |
| 4 | 2 |
CodePudding user response：
maybe a simple left join like this can help you in filtering
select
I.*
from Invoice I
left outer join Contract C -- left join gets even the NULL contractid invoices
on C.ContractID= I.ContractID
where
C.[Contract Start Timestamp] IS NULL
OR C.[Contract Start Timestamp]< '2021-03-01'