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

Question

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.

Answer 1

You can use two corelated subqueries as you have tried, try like so:

Select p.Name
 ,(select l.Name from Location l where l.Id = p.StartLocationId) as NameStartLocation
 ,(select l.Name from Location l where l.Id = p.FinalLocationId) as NameFinalLocation
from Product p