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.
>Solution :
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