CODEWITHSUNDEEP
sqlsql-serversubquerymultiple-columns
Andrey
Andrey

Reputation: 517

Subquery with multiple fields

I have SQL query:

SELECT DISTINCT t1.inn, 
                t1.idx, 
                (SELECT TOP 1 adr 
                 FROM   t2 
                 WHERE  t2.idx = t1.idx 
                        AND t2.inn = t1.inn) ADR, 
                (SELECT TOP 1 name 
                 FROM   t2 
                 WHERE  t2.idx = t1.idx 
                        AND t2.inn = t1.inn) NAME 
FROM   t1

Is it possible to make one subquery instead two? (i.e. select TOP 1 ADR and NAME simultaneously). Server: MS SQL 2008-2012.

Upvotes: 1

Views: 135

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can do this with cross apply:

select distinct t1.INN, t1.IDX, t2.adr, t2.name
from t1 cross apply
     (select top 1 adr, name
      from t2
      where t2.idx = t1.idx and t2.inn = t1.inn
     ) t2;

However, you are selecting one row without an order by. If there is only one row that matches, then you can do this with a regular join.

Upvotes: 5

Related Questions