Tsql - logic to to get the Expected result

1

Hi All,

is that possible to achieve this.
Thanks is advance.

drop table #tab if exists
drop table #result if exists

create table #tab
(SourceAccID varchar(100) , DestinationAccID varchar(100))

create table #Expectedresult(Expres varchar(100))

insert into #tab
select 'A' , 'B' union all
select 'A' , 'C'

select * from #tab

Expected output :

declare @inputvar varchar(100) = 'B'
--when @inputvar as 'B'
Expres
A
C

declare @inputvar varchar(100) = 'C'
--when @inputvar as 'C'
Expres
A
B

declare @inputvar varchar(100) = 'A'
--when @inputvar as 'A'
Expres
B
C

2

I'm not an expert on this subject. But it reminds me of:

This:
hierarchyid (Transact-SQL) - SQL Server | Microsoft Learn

And maybe even this:
Graph processing - SQL Server and Azure SQL Database | Microsoft Learn

3 
SELECT SourceAccID AS Expres FROM #tab
UNION
SELECT DestinationAccID FROM #tab
EXCEPT
SELECT @inputvar
1 Like
4

Hi @robert_volk , thanks for spending your precious time.

My bad i have not mentioned entire table scenario.

For example in table #tab

Insert into #tab
Select 'A','B' union all
Select 'A','C' union all
Select 'D','F' union all
Select 'D','G' union all
Select 'Z','E' union all
Select 'Z','S'

If you observe, one same SourceAccID is linked with two different DestinationAccID.

Similarly there will be millions of unique records in this way.

If i get an input as an 'S' i need to retrive 'Z' and 'E'
If i get an input as an 'Z' i need to retrive 'S' and 'E'
If i get an input as an 'E' i need to retrive 'Z' and 'S'
If i get an input as an 'D' i need to retrive 'F' and 'G'
If i get an input as an 'F' i need to retrive 'D' and 'G'
If i get an input as an 'G' i need to retrive 'F' and 'D'
If i get an input as an 'B' i need to retrive 'A' and 'C
'If i get an input as an 'A' i need to retrive 'B' and 'C'
If i get an input as an 'C' i need to retrive 'A' and 'B'

Thanks in advance.

5

plz let me know if you required any more information, Thanks.

6 
DROP TABLE IF EXISTS #tab;

CREATE TABLE #tab (SourceAccID varchar(100) , DestinationAccID varchar(100));

INSERT INTO #tab
SELECT 'A','B' UNION
SELECT 'A','C' UNION
SELECT 'D','F' UNION
SELECT 'D','G' UNION
SELECT 'Z','E' UNION
SELECT 'Z','S';

DECLARE @AccID VARCHAR(100)
SET @AccID='B';

WITH FirstLevel AS
(
SELECT 
	SourceAccID,  
	DestinationAccID,
	CASE WHEN DestinationAccID=@AccID THEN SourceAccID ELSE DestinationAccID END AS Result
FROM #tab
WHERE SourceAccID=@AccID OR DestinationAccID=@AccID
)
SELECT
	FirstLevel.Result 
FROM FirstLevel
UNION
SELECT
	SecondLevel.DestinationAccID
FROM FirstLevel 
 INNER JOIN #Tab AS SecondLevel
	ON FirstLevel.SourceAccID=SecondLevel.SourceAccID
WHERE SecondLevel.DestinationAccID <> @AccID;
1 Like
7

Hi @RogierPronk ,

thanks a lot for your time.
its working as expected.

if i do the below changes to query will it impact to the output ?

DECLARE @AccID VARCHAR(100)
SET @AccID='B';

WITH FirstLevel AS
(
SELECT
SourceAccID,
DestinationAccID,
CASE WHEN DestinationAccID=@AccID THEN SourceAccID ELSE DestinationAccID END AS Result
FROM #tab
WHERE SourceAccID=@AccID

UNION ALL

SELECT
SourceAccID,
DestinationAccID,
CASE WHEN DestinationAccID=@AccID THEN SourceAccID ELSE DestinationAccID END AS Result
FROM #tab
WHERE DestinationAccID=@AccID

)
SELECT
FirstLevel.Result
FROM FirstLevel
UNION
SELECT
SecondLevel.DestinationAccID
FROM FirstLevel
INNER JOIN #Tab AS SecondLevel
ON FirstLevel.SourceAccID=SecondLevel.SourceAccID
WHERE SecondLevel.DestinationAccID <> @AccID;

8

Based on the example you gave it won't have any impact on the output as a SourceAccID <> DestinationAccID. To be sure you should test it yourself as I suspect a negative performance impact.

9

thank you so much.