CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
Waqar Ahmed
Waqar Ahmed

Reputation: 5068

Multiple column of same table in another table

I am working on sample railway database. I am stuck at retrieving some data. Table structure is

Train :

TrainId | TrainName | FromStationId | ToStationId | DeptTime | ArrivalTime
...
...

RouteDetail :

Id | TrainId | RouteOrder | FromStationId | ToStationId | TrackId
...
...

Station : 

Id | name
...
...

Now I'm trying to retrieve this data but I haven't been successful yet.

TrainName | RouteOrder | FromStationName | ToStationName

Will really appreciate any help

Thanks.

Upvotes: 2

Views: 56

Answers (2)

Donal
Donal

Reputation: 32713

select t.trainname, r.routeorder, sfrom.name as fromstationname, sto.name as tostationname
from train t
inner join routedetail r on r.trainid = t.trainid
left join station sfrom on sfrom.id = fromstationid
left join station sto on sto.id = tostationid

Upvotes: 2

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33381

Something like this. If no, you must provide more details.

SELECT 
    T.TrainName
    , RD.RoutOrder
    , S1.name as FromStationName
    , S2.name as ToStationName
FROM Train T
  LEFT JOIN RouteDetail RD
    ON T.TrainId = RD.TrainId
  LEFT JOIN Station S1
    ON RD.FromStationId = S1.Id
  LEFT JOIN Station S2
    ON RD.ToStationId = S2.Id

Upvotes: 4

Related Questions