select only same id and different group data

Question

select only same id and different group data

Lily June 101

I would like to get same transfer id with different group as the following script

GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TransferDetail](
[Account] [float] NULL,
[TransferId] nvarchar NULL,
[TransferType] nvarchar NULL,
[TransferDate] [datetime] NULL,
[Description] nvarchar NULL,
[Currency] [float] NULL,
[Amount] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TransferDetail] ([Account], [TransferId], [TransferType], [TransferDate], [Description], [Currency], [Amount]) VALUES (11111, N'ddd', N'From', CAST(0x0000AD9A00000000 AS DateTime), N'Transfer 11111', 702, -300)
INSERT [dbo].[TransferDetail] ([Account], [TransferId], [TransferType], [TransferDate], [Description], [Currency], [Amount]) VALUES (11111, N'aaa', N'From', CAST(0x0000AD9A00000000 AS DateTime), N'Transfer from 11111', 702, -100)
INSERT [dbo].[TransferDetail] ([Account], [TransferId], [TransferType], [TransferDate], [Description], [Currency], [Amount]) VALUES (11111, N'aaa', N'To', CAST(0x0000AD9A00000000 AS DateTime), N'Transfer To 11111', 840, 74.33)
INSERT [dbo].[TransferDetail] ([Account], [TransferId], [TransferType], [TransferDate], [Description], [Currency], [Amount]) VALUES (11111, N'bbb', N'From', CAST(0x0000AD9A00000000 AS DateTime), N'Transfer from 11111', 702, -200)
INSERT [dbo].[TransferDetail] ([Account], [TransferId], [TransferType], [TransferDate], [Description], [Currency], [Amount]) VALUES (11111, N'bbb', N'To', CAST(0x0000AD9A00000000 AS DateTime), N'Transfer To 11111', 840, 148.65)
INSERT [dbo].[TransferDetail] ([Account], [TransferId], [TransferType], [TransferDate], [Description], [Currency], [Amount]) VALUES (22222, N'ccc', N'From', CAST(0x0000AD9A00000000 AS DateTime), N'Transfer From 22222', 840, -200)

Here're the table data

I would like to get highlighted color records

Currently,I extract like that
Query 1: not got need result

select * from Transferdetail where TransferType ='From' and CURRENCY ='702' Or TransferType ='To' and CURRENCY ='840'

Query 2: got need result but I would like know more ways

select * from Transferdetail where TransferType IN ('From','To') and CURRENCY IN ('702','840')
and TransferId In (select TransferId from Transferdetail where TransferType ='To' and CURRENCY ='840' )

pls any way suggest.

Viorel 127K

Maybe you can also do this:

select *
from @Transferdetail t
where exists( select * from @Transferdetail where TransferId = t.TransferId and TransferType='To')

Lily June 101 Reputation points

2021-09-18T06:12:42.91+00:00

I would like to add more detail ,currency can be transfer like that
From 702 To 840
From 840 To 702 ,
From 702 To 978,
From 978 To 702

-,I would like to get pair transferId such as FromTo (840 <->702 )
Thanks.

Viorel 127K

It seems that you already have an answer, which is based on INNER JOIN. Just adjust it for your needs, for example:

select 
    f.Account, 
    f.TransferDate,
    f.Amount as AmountFrom, f.Currency as CurrencyFrom,
    t.Amount as AmountTo, t.Currency as CurrencyTo
from TransferDetail f
inner join TransferDetail t on f.TransferId=t.TransferId and f.TransferType='From' and t.TransferType='To'

If you need something else, then show the desired format of the output.

Lily June 101 Reputation points

2021-09-20T06:10:01.533+00:00

hello,@Viorel ,I really appreciate for your answer .I got answer
what I need result from @EchoLiu-MSFT . Thanks to you also.

Answer accepted by question author

EchoLiu-MSFT 14,626

Hi @Lily June ,

Does your FROM-TO only have the four data pairs you mentioned above? If yes, please refer to:

 SELECT * FROM Transferdetail   
 WHERE TransferId IN(  
 SELECT TransferId  FROM Transferdetail  
 WHERE TransferType='From' AND CURRENCY ='702'  
 INTERSECT  
 SELECT TransferId  FROM Transferdetail  
 WHERE TransferType='To'AND CURRENCY IN ('840','978')  
 UNION ALL  
 SELECT TransferId  FROM Transferdetail  
 WHERE TransferType='From' AND CURRENCY IN ('840','978')  
 INTERSECT  
 SELECT TransferId  FROM Transferdetail  
 WHERE TransferType='To'AND CURRENCY ='702')

Output:

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.

Lily June 101 Reputation points

2021-09-20T04:52:24.637+00:00

QUERY 1:
SELECT * FROM Transferdetail
WHERE TransferId IN(
SELECT TransferId FROM Transferdetail
WHERE TransferType='From' AND CURRENCY ='702'
INTERSECT
SELECT TransferId FROM Transferdetail
WHERE TransferType='To'AND CURRENCY IN ('702','840')
UNION ALL
SELECT TransferId FROM Transferdetail
WHERE TransferType='From' AND CURRENCY IN ('702','840')
INTERSECT
SELECT TransferId FROM Transferdetail
WHERE TransferType='To'AND CURRENCY ='702')

QUERY 2:

SELECT * FROM Transferdetail
WHERE TransferId IN(
SELECT TransferId FROM Transferdetail
WHERE CURRENCY=702)
AND
TransferId IN(
SELECT TransferId FROM Transferdetail
WHERE CURRENCY=840)

Hello,@EchoLiu-MSFT ,Both query are working well what I need result.Which way is more better for performance.pls suggest me.
EchoLiu-MSFT 14,626 Reputation points

2021-09-20T05:54:23.423+00:00

The second method seems to be unable to satisfy the two situations in the picture.
If both methods are correct for you, I suggest choosing the second one. But in fact, if your data volume is not very large, the difference in performance is relatively small.

Regards
Echo
Lily June 101 Reputation points

2021-09-20T06:10:57.757+00:00

Thanks @EchoLiu-MSFT , I really appreciate for your answer .

2 additional answers

Your answer

Viorel 127K Reputation points

2021-09-17T19:57:38.863+00:00

Maybe you can also do this:

select * from @Transferdetail t where exists( select * from @Transferdetail where TransferId = t.TransferId and TransferType='To')
Lily June 101 Reputation points

2021-09-18T06:12:42.91+00:00

I would like to add more detail ,currency can be transfer like that
From 702 To 840
From 840 To 702 ,
From 702 To 978,
From 978 To 702

-,I would like to get pair transferId such as FromTo (840 <->702 )
Thanks.
Viorel 127K Reputation points

2021-09-18T07:46:21.04+00:00

It seems that you already have an answer, which is based on INNER JOIN. Just adjust it for your needs, for example:

select f.Account, f.TransferDate, f.Amount as AmountFrom, f.Currency as CurrencyFrom, t.Amount as AmountTo, t.Currency as CurrencyTo from TransferDetail f inner join TransferDetail t on f.TransferId=t.TransferId and f.TransferType='From' and t.TransferType='To'

If you need something else, then show the desired format of the output.
Lily June 101 Reputation points

2021-09-20T06:10:01.533+00:00

hello,@Viorel ,I really appreciate for your answer .I got answer
what I need result from @EchoLiu-MSFT . Thanks to you also.
Lily June 101 Reputation points

2021-09-20T04:52:24.637+00:00

QUERY 1:
SELECT * FROM Transferdetail
WHERE TransferId IN(
SELECT TransferId FROM Transferdetail
WHERE TransferType='From' AND CURRENCY ='702'
INTERSECT
SELECT TransferId FROM Transferdetail
WHERE TransferType='To'AND CURRENCY IN ('702','840')
UNION ALL
SELECT TransferId FROM Transferdetail
WHERE TransferType='From' AND CURRENCY IN ('702','840')
INTERSECT
SELECT TransferId FROM Transferdetail
WHERE TransferType='To'AND CURRENCY ='702')

QUERY 2:

SELECT * FROM Transferdetail
WHERE TransferId IN(
SELECT TransferId FROM Transferdetail
WHERE CURRENCY=702)
AND
TransferId IN(
SELECT TransferId FROM Transferdetail
WHERE CURRENCY=840)

Hello,@EchoLiu-MSFT ,Both query are working well what I need result.Which way is more better for performance.pls suggest me.
EchoLiu-MSFT 14,626 Reputation points

2021-09-20T05:54:23.423+00:00

The second method seems to be unable to satisfy the two situations in the picture.
If both methods are correct for you, I suggest choosing the second one. But in fact, if your data volume is not very large, the difference in performance is relatively small.

Regards
Echo
Lily June 101 Reputation points

2021-09-20T06:10:57.757+00:00

Thanks @EchoLiu-MSFT , I really appreciate for your answer .

Answer 1

Olaf Helper 47,621

Not very clear, but I guess you mean something like this:

select *
from #Transferdetail AS tdFROM
     INNER JOIN
     #Transferdetail AS tdTO
         on tdFROM.TransferId = tdTO.TransferId
where tdFROM.TransferType ='From' and tdFROM.CURRENCY ='702'
      AND tdTO.TransferType ='To' and tdTO.CURRENCY ='840'

Lily June 101 Reputation points

2021-09-20T06:09:44.453+00:00

hello,@Olaf Helper ,I really appreciate for your answer .I got answer
what I need result from @EchoLiu-MSFT . Thanks to you also.

Answer 2

EchoLiu-MSFT 14,626

Hi @Lily June ,

Please check:

SELECT [Account],[TransferId],[TransferType],  
[TransferDate],[Description],[Currency],  
[Amount] FROM (SELECT *,COUNT(TransferId)   
               OVER(PARTITION BY TransferId) num  
               FROM Transferdetail) t   
WHERE num>1

Or:

SELECT * FROM Transferdetail   
WHERE TransferId IN(  
SELECT TransferId  FROM Transferdetail  
WHERE TransferType='From'  
INTERSECT  
SELECT TransferId  FROM Transferdetail  
WHERE TransferType='To')

Or:

SELECT * FROM Transferdetail   
WHERE TransferId IN(  
SELECT TransferId FROM Transferdetail  
WHERE CURRENCY=702)  
AND   
TransferId IN(  
SELECT TransferId FROM Transferdetail  
WHERE CURRENCY=840)

If you have any question, please feel free to let me know.

Regards
Echo

If the answer is helpful, please click "Accept Answer" and upvote it.

Lily June 101 Reputation points

2021-09-18T06:18:34.223+00:00

Hi,@EchoLiu-MSFT

I feel like my question is need to more explain so I would like to add more detail ,currency can be transfer like that
From 702 To 840
From 840 To 702 ,
From 702 To 978,
From 978 To 702

-,I would like to extract pair transferId such as FromTo (840 <->702 ),FromTo (702 <->978) ,FromTo (840<->978)
Thanks.

Share via

select only same id and different group data

2 additional answers

Your answer