Home Select rows with same customer id but different transaction id in the same table?

Questions

Select rows with same customer id but different transaction id in the same table?

September 3, 2022

Every single item sold will come to a new row with date, transaction id, customer id, product name and quantity. I want to find out customers who have made at least 2 separate transactions in August. Thanks alot!

Table:
| Date | transaction_id | customer_id | product_name | qty |
|:—- |:——:| —–:| —–:|—–:|
| 2022-08-05 10:44:59.000 | 5-123 | 888 |A|1|
| 2022-08-05 10:44:59.000 | 5-123 | 888 |A|1|
| 2022-08-05 18:32:22.000 | 5-179 | 174 |ZZ|1|
|2022-08-06 10:32:22.000| 6-264 | 223 |QA|1|
|2022-08-06 10:32:52.000| 6-264 | 223 |QB|1|
|2022-08-06 12:57:12.000| 6-365 | 379 |A|1|
|2022-08-07 20:16:38.000| 7-974 | 888 |V|1|
|2022-08-08 17:48:11.000| 8-276 | 732 |R|1|
|2022-08-09 13:29:43.000| 9-390 | 623 |G|1|
|2022-08-10 09:33:57.000| 10-862 | 623 |TT|1|
|2022-08-11 16:23:31.000| 11-348 | 623 |XD|1|

Expected outcome:
| Date | transaction_id | customer_id | product_name | qty |
|:—- |:——:| —–:| —–:|—–:|
| 2022-08-05 10:44:59.000 | 5-123 | 888 |A|1|
| 2022-08-05 10:44:59.000 | 5-123 | 888 |A|1|
|2022-08-07 20:16:38.000| 7-974 | 888 |V|1|
|2022-08-09 13:29:43.000| 9-390 | 623 |G|1|
|2022-08-10 09:33:57.000| 10-862 | 623 |TT|1|
|2022-08-11 16:23:31.000| 11-348 | 623 |XD|1|

>Solution :

You may use Exists with correlated subquery as the following:

Select T.Date_, T.transaction_id, T.customer_id, T.product_name, T.qty
From table_name T
Where Exists(Select 1 From table_name D 
             Where D.customer_id = T.customer_id And
                   D.transaction_id <> T.transaction_id
             )
And T.Date_ Between '2022-08-01 00:00:00' And '2022-08-31 00:00:00'

See a demo from db<>fiddle.