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

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.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

You can join the Location table multiple times:

select p.Product
    , sLoc.Name NameStartLocation
    , fLoc.Name NameFinalLocation
from Product p
left join [Location] sLoc
    on p.StartLocationId = sLoc.id
left join [Location] fLoc
    on p.FinalLocationId = fLoc.id