CODEWITHSUNDEEP
sqlsql-server
user1781272
user1781272

Reputation: 932

SQL: Select rows in a table by filtering multiple columns from the same table by a 3 column select result

I have a table where I want to filter all rows that have a Code,Life and TC equal to the results of a select query on the same table filtered by ID

ID  Code|Life|TC|PORT
62  XX101   1   1   1
63  XX101   1   1   2
64  AB123   1   1   1
65  AB123   1   1   2
66  AB123   1   1   3
67  CD321   1   1   1
68  CD321   1   1   2

This is the best I have come up with but it doesn't seem to be very efficient.

select ID from #table
where Code = (Select Code from #table where ID = @Port1) and 
      Life = (Select Life from #table where ID = @Port1) and 
      TC = (Select TC from #table where ID = @Port1)

Upvotes: 1

Views: 2105

Answers (4)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

Using window functions:

;WITH CTE AS (
  SELECT *, RANK() OVER (ORDER BY [Code], [Life], [TC]) AS grp
  FROM mytable 
), CTE2 AS (SELECT grp FROM CTE WHERE ID = @Port1)
SELECT *
FROM CTE
WHERE grp = (SELECT grp FROM CTE2)

The above query finds the [Code], [Life], [TC] partition to which row with ID = @Port1 belongs and then selects all rows of this partition.

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

Here is the query you need:

select t2.* 
from #table t1
join #table t2 on t1.Code = t2.Code and 
                  t1.Life = t2.Life and 
                  t1.TC = t2.TC and 
                  t1.PORT = t2.PORT
where t1.id = @Port1

With cross apply:

select ca.* 
from #table t1
cross apply (select * from #table t2 where t1.Code = t2.Code and 
                                           t1.Life = t2.Life and 
                                           t1.TC = t2.TC and 
                                           t1.PORT = t2.PORT) ca
where where t1.id = @Port1

With cte:

with cte as(select * from #table where id = @Port1)
select t.* 
from #table t
join cte c on t.Code = c.Code and 
              t.Life = c.Life and 
              t.TC = c.TC and 
              t.PORT = c.PORT

Upvotes: 2

Nickthename
Nickthename

Reputation: 259

Your code looks to provide the same result of

SELECT ID 
FROM #table AS tbl1
 INNER JOIM#table AS tbl2 on
   tbl2.ID =@Port1 AND 
   tbl1.Life =tbl2.Life AND 
   tbl1.TC =tbl2.TC

but it's more expensive You are asking always for the same record in the selects under the where clause. Then each time you pick a different field to match. But pay attention because if there is more than one record with that ID your query gives error because, since you used the = operator it expects only one instance of the field you are checking.

Upvotes: 1

JamieD77
JamieD77

Reputation: 13949

You could use an EXIST statement for this scenario

SELECT
    ID
FROM
    #table t1
WHERE
    EXISTS ( SELECT
                *
             FROM
                #table t2
             WHERE
                t2.ID = @Port1
                AND t2.Code = t1.Code
                AND t2.Life = t1.Life
                AND t2.TC = t1.TC )

Upvotes: 1

Related Questions