Home Select one specific value from several values in subquery select in select clause

Questions

Select one specific value from several values in subquery select in select clause

February 17, 2022

I have this case in T-SQL.

TABLE 1 – Product (id, Name, StartLocationId, FinalLocationId).

StartLocationId and FinalLocationId are identifiers to Location.

Id Product    StartLocationId  FinalLocationId
1  Porsche    1                 2
2  Bentley    2                 3  
3  Maseratti  3                 1

TABLE 2 – Location (Id, Name)

Id   Name
1    Garage Motor
2    Firestone
3    Michelin

I need to get these:

Product   NameStartLocation   NameFinalLocation
Porsche    Garage Motor        Firestone
Bentley    Firestone           Michelin
Maseratti  Michelin            Firestone

I tried with:

Select 
  Product.Name
 ,(select Location.Name 
   from Product inner join 
   Location ON Product.StartLocationId = Location.Id)
 ,(select Location.Name 
   from Product inner join 
   Location ON Product.FinalLocationId = Location.Id)
from Product

but it is not possible to have several values in subquery, and I cannot use top neither order by to get desired table.

>Solution :

try like below using join

select p.Product,ls.name as startlocation,
lf.name as finallocation
 from product p 
left join Location ls on p.StartLocationId=ls.id
left join Location lf on p.FinalLocationId=lf.id