CODEWITHSUNDEEP
sqlsql-servert-sql
DeeKayy90
DeeKayy90

Reputation: 857

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.

Upvotes: 1

Views: 1283

Answers (2)

Justin
Justin

Reputation: 9724

Query: LINK

SELECT h.*
FROM Header h
LEFT JOIN Lines l
  ON l.HeaderID = h.ID
  AND l.LineID IN(1, 2)
WHERE l.HeaderID is null

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

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

Upvotes: 3

Related Questions