CODEWITHSUNDEEP
mysqlselectsearch
kakarot
kakarot

Reputation: 45

Select data based on condition that match multiple column values

I am quite new to the SQL world. Wondering if someone could shed some light on the situation below. I am trying to get a list of account numbers that contain both transaction codes 150 and 300 for that particular account.

Table:Transactions

+-------+--------+----------+
| AcNum | TxCode |   Date   |
+-------+--------+----------+
|   111 |    150 | 1/1/2019 |
|   111 |    100 | 1/1/2019 |
|   111 |    100 | 1/2/2019 |
|   123 |    100 | 1/1/2019 |
|   123 |    200 | 1/1/2019 |
|   123 |    300 | 1/1/2019 |
|   123 |    300 | 1/2/2019 |
|   222 |    150 | 1/1/2019 |
|   222 |    150 | 1/2/2019 |
|   333 |    300 | 1/2/2019 |
|   333 |    150 | 1/3/2019 |
|   444 |    100 | 1/1/2019 |
|   444 |    300 | 1/1/2019 |
|   444 |    150 | 1/1/2019 |
|   456 |    100 | 1/1/2019 |
|   456 |    200 | 1/1/2019 |
|   456 |    300 | 1/1/2019 |
|   456 |    150 | 1/2/2019 |
|   789 |    100 | 1/1/2019 |
|   789 |    100 | 1/2/2019 |
|   789 |    300 | 1/1/2019 |
+-------+--------+----------+

Here is the SQL query I created but it returns accounts that have 150 and 300 but it also contains the account that just have one of those transactions. I am looking for accounts that contain both transactions.

SELECT acnum
FROM   transactions
WHERE  txcode IN ( '150', '300' )

Desired result would be as follows

AcNum
333
444
456

Result I am getting is as follows

AcNum
111
123
222
333
444
444
456
789

Upvotes: 2

Views: 42

Answers (2)

forpas
forpas

Reputation: 164089

You must GROUP BY acnum and count the distinct txcode to be 2:

SELECT acnum
FROM   transactions
WHERE  txcode IN ( '150', '300' )
GROUP BY acnum
HAVING COUNT(DISTINCT txcode) = 2

See the demo.
Results:

| acnum |
| ----- |
| 333   |
| 444   |
| 456   |

Upvotes: 2

Bart Friederichs
Bart Friederichs

Reputation: 33511

You could also use subselects in the IN:

SELECT DISTINCT acnum
FROM transactions
WHERE 
   acnum IN (SELECT acnum FROM transactions WHERE tx=150) AND
   acnum IN (SELECT acnum FROM transactions WHERE tx=300)

See it in action.

Upvotes: 0

Related Questions