How to find a record where a column from the table only contains one of two possible records-CodePudding

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

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

It is possible in this table for an invoice number to only contain a record with a transaction code of 10

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

Example 'Invoice Table'

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

Expected Result from Example Table Output This is what i would expect from the query on the table from Invoice Table because invoice CAN000700798 only contains a transaction code 10 and not a 10 & 20.

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

I need to write a query that looks at the invoice number and looks to see if it contains both records of having a transaction code 10 & 11.

If the invoice number only contains transaction code 10, return the record.

I have looked at several blogs and posts but cannot find anything that yields the result that I am looking for. Appreciate your help. Please let me know if you need more information.

CodePudding user response：

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

Could you please try if the above query is suitable for you

CodePudding user response：

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