CODEWITHSUNDEEP
sqlt-sql
user2828970
user2828970

Reputation: 33

Select Rows having values matching in 2 other columns

Table

Id   ||   IdFrom  ||  IdTo
--------------------------
1    ||    null   ||  2
2    ||    1      ||  null
3    ||    null   ||  5
4    ||    null   ||  6
5    ||    3      ||  9
6    ||    4      ||  7
7    ||    6      ||  null
8    ||    null   ||  null
9    ||    5      ||  10
10   ||    9      ||  null
947  ||    null   ||  949
949  ||    947    ||  952
950  ||    null   ||  951
951  ||    950    ||  952
952  ||    951    ||  null

Need to get all rows or just specifically the Ids that are found between all 3 columns when specifying a given Id. So a SELECT (all ids found in the IdFrom or IdTo and Those IdFrom's or IdTo's are in other IdFrom's or IdTo's)

Results when searching for Id 1 would give results of Ids 1 and 2

Results when searching for Id 2 would give results of Ids 1 and 2

Results when searching for Id 3,5,9, or 10 would give results of Ids 3,5,9, and 10

Results when searching for Id 4,6, or 7 would give results of Ids 4,6,and 7

My current search is an iteration getting IdFrom and IdTo for Id, putting those found Id's into a tmp table and iterating back again searching for matches until no more distinct Ids are found. It works but is extremely ugly and takes longer then probably could...

Came across a query that can get all rows that have matching but not specifying for a particular id 

DECLARE @SearchForId int = 1
SELECT 
    t1.ID,t1.IdFROM,t1.IdTO
FROM SomeTable t1
WHERE
    (
        EXISTS(SELECT Id FROM SomeTable tblFROM WHERE tblFROM.IdFROM = t1.Id) OR
        EXISTS(SELECT Id FROM SomeTable tblTO WHERE IdTO.IDTRANSFEREDTO = t1.Id)
    )  
AND Id = 1 <<-- this part just gives that id obvious but without it, it gets everything in the entire table

EDIT: added new ids (947-952). The previous selected solution did provide ids 947 and 949 but missing 950,951,952. Tried adding another couple cte's like the previous solution to give all ids 947,949,950,951,952 but only giving 947 and 949. How to get all 5. That solution was much quicker by almost by 25x. Would like to keep it and get remainder id's

Upvotes: 0

Views: 67

Answers (2)

Amir Molaei
Amir Molaei

Reputation: 3810

You need to do it using two recursive common table expressions.

declare @id int
set @id = 2  

;WITH CTE1 
    AS(
        SELECT c.*
        FROM tbl c
        WHERE c.Id = @id

        UNION ALL

        SELECT p.*
        FROM CTE1 cte1_alias
        INNER JOIN  tbl p 
          ON p.IdFrom = cte1_alias.Id
    ),
    CTE2
    AS(
        SELECT c.*
        FROM tbl c
        WHERE c.Id = @id

        UNION ALL

        SELECT p.*
        FROM CTE2 cte2_alias
        INNER JOIN  tbl p 
          ON p.IdTo = cte2_alias.Id
    )

SELECT Id FROM CTE1
Union 
SELECT Id FROM CTE2

Upvotes: 1

vahdet
vahdet

Reputation: 6729

As I understand ORing the columns to be equal to the given value would be enough:

Declare @prm int = 1
Select Id
From SomeTable
Where Id=@prm Or IdFrom=@prm Or IdTo=@prm

or, for a different flavour, using in among columns 

Declare @prm int = 1
Select Id
From SomeTable
Where @prm in (Id, IdFrom, IdTo)

Upvotes: 0

Related Questions