CODEWITHSUNDEEP
sqlexists
KCS
KCS

Reputation: 59

How to find rows for which another related row does not exist

I have a table that consists of an invoice number and a transaction code. A single invoice can contain transaction code of 10 and 11:

+------------------+-----------------+
| Transaction Code |  Invoice Number |
+------------------+-----------------+
|     10           |   CAN000700798  |
|     20           |   CAN000700798  |
+------------------+-----------------+

It is possible for an invoice to only contain a record with a transaction code of 10:

+------------------+-----------------+
| Transaction Code |  Invoice Number |
+------------------+-----------------+
|     10           |   CAN000700798  |
+------------------+-----------------+

Example of invoice table showing both cases:

+------------------+-----------------+
| Transaction Code |  Invoice Number |
+------------------+-----------------+
|     10           |   CAN000700798  |
+------------------+-----------------+ 
|     10           |   CAN000900999  |
|     20           |   CAN000900999  |
+------------------+-----------------+ 
|     10           |   CAN000700777  |
|     20           |   CAN000700777  |
+------------------+-----------------+

This is what I would expect from the query on the invoice table; since invoice CAN000700798 only contains a transaction code 10 and not a 10 and a 20.

+------------------+-----------------+
| Transaction Code |  Invoice Number |
+------------------+-----------------+
|     10           |   CAN000700798  |
+------------------+-----------------+

I need to write a query that looks at the invoices and checks if it contains both records having a transaction code 10 and 11. If the invoice only contains transaction code 10, return the record.

Upvotes: 0

Views: 85

Answers (2)

Sergey
Sergey

Reputation: 5217

Could you please try if the following query is suitable for you:

SELECT T.TransactionCode, T.InvoiceNumber
FROM InvoiceTable AS T
WHERE T.TransactionCode = 10 AND NOT EXISTS (
    SELECT 1
    FROM InvoiceTable AS X
    WHERE X.InvoiceNumber = T.InvoiceNumber AND X.TransactionCode <> 10
)

Upvotes: 2

Salman Arshad
Salman Arshad

Reputation: 272106

You can use GROUP BY and HAVING clause:

SELECT InvoiceNumber, MIN(TransactionCode) AS TransactionCode
FROM t
GROUP BY InvoiceNumber
HAVING MIN(TransactionCode) = 10
AND    MAX(TransactionCode) = 10

Upvotes: 1

Related Questions