DeeKayy90 DeeKayy90 - 7 months ago 9
SQL Question

SQL - Select where record-set does not contain a value

I am not sure the title reflects what I am searching for accurately.

I have a header table, and a lines table. For each header, there can be multiple line records.

If the lines table doesn't contain the line value for a header record, I want to return that header record.

Example:

Header Table -

| ID | xyz...|
| 1 | abc |
| 2 | abc |


Lines Table -

| LineID | HeaderID | xyz...|
| 1 | 1 | abc |
| 2 | 1 | abc |
| 3 | 1 | abc |
| 3 | 2 | abc |
| 4 | 2 | abc |
| 5 | 2 | abc |


In this case, I am looking to return Header Record 2 if I search for the record that doesn't contain a line ID of 1 or 2.

I can't think of the query for the life of me, even though I know it's staring me right in the face.

When I think of queries to try, I will post them to show my workings, but I am hoping someone has an answer for this.

Answer

A simple NOT EXISTS will do the trick:

SELECT *
FROM Header h
WHERE NOT EXISTS(
    SELECT 1
    FROM Lines l
    WHERE
        l.HeaderID = h.ID
        AND l.LineID IN(1, 2)
)

ONLINE DEMO

Comments