CODEWITHSUNDEEP
sqlsql-servert-sql
sachin desilva
sachin desilva

Reputation: 33

How to show values that has a suitable pair in sql server

I have a table like this

---------------------------------------------
Id | TransactionId | Amount | Account| crdr | 
---------------------------------------------
1  |       1       | 100    | 11111  |   1  |
2  |       2       | 130    | 13133  |   1  |
3  |       1       | 100    | 12111  |   2  |
4  |       2       | 130    | 13233  |   2  |
5  |       2       | 110    | 12122  |   1  |

What I need to display is, show these records as pairs (I have grouped them by transactionid, Amount). 

SELECT TransactionId ,Amount , Account, CrDr
FROM Table1 ORDER BY TransactionId  ASC,Amount  ASC, CrDr ASC

But I want to ignore the records which dont have a pair, as a Example for this above records set result should be like this

---------------------------------------------
TransactionId | Amount | Account| crdr | 
---------------------------------------------
      1       | 100    | 11111  |   1  |
      1       | 100    | 12111  |   2  |
      2       | 130    | 13133  |   1  |
      2       | 130    | 13233  |   2  |

Can someone suggest a solution for this.

Upvotes: 3

Views: 67

Answers (3)

harshit gupta
harshit gupta

Reputation: 32

Try this: This this simplest solution of this problem

;with cte
as
(
    select TransactionId, Amount
    from Table1
    group by TransactionId, Amount
    having count(*) > 1
)
select * 
from Table1 t
inner join cte c on t.TransactionId = c.TransactionId and t.Amount = c.Amount

Upvotes: 0

gotqn
gotqn

Reputation: 43636

Try this:

DECLARE @DataSource TABLE
(
     [Id] INT
    ,[TransactionId] INT
    ,[Amount] INT
    ,[Account] INT 
    ,[crdr] INT
);

INSERT INTO @DataSource ([Id], [TransactionId], [Amount], [Account], [crdr])
VALUES (1, 1, 100, 11111, 1)
      ,(2, 2, 130, 13133, 1)
      ,(3, 1, 100, 12111, 2)
      ,(4, 2, 130, 13233, 2)
      ,(5, 2, 110, 12122, 1);

WITH DataSource AS
(
    SELECT *
          ,COUNT(*)  OVER (PARTITION BY [TransactionId], [Amount]) AS [Count]
    FROM @DataSource
)
SELECT *
FROM DataSource
WHERE [Count] = 2
ORDER BY TransactionId  ASC,Amount  ASC, CrDr ASC;

Upvotes: 0

GMB
GMB

Reputation: 222492

You could use a correlated subquery with a NOT EXISTS condition to ensure that another record exists with the same TransactionId and Amount:

SELECT TransactionId ,Amount , Account, CrDr
FROM Table1 t
WHERE EXISTS (
    SELECT 1
    FROM Table1 t1
    WHERE 
        t.id <> t1.id 
        AND t.TransactionId = t1.TransactionId
        AND t.Amount = t1.Amount
)
ORDER BY TransactionId  ASC,Amount  ASC, CrDr ASC

Demo on DB Fiddle:

TransactionId | Amount | Account | CrDr
------------: | -----: | ------: | ---:
            1 |    100 |   11111 |    1
            1 |    100 |   12111 |    2
            2 |    130 |   13133 |    1
            2 |    130 |   13233 |    2

Upvotes: 1

Related Questions