CODEWITHSUNDEEP
sqlsql-server
Davevmb
Davevmb

Reputation: 85

SQL is this possible...it probably is

So i have 2 tables

Places

PlaceID   varchar  PK
PlaceName varchar

Trips

TripID      int   PK
Depart      TIME
DepartPlc   varchar
ArrivalPlc  varchar
ArrivalTime TIME

DepartPlc and ArrivalPlc both have PlaceID's stored.

I want to select the data from trips, but show de PlaceName data that corresponds to the ID stored in DepartPlc and ArrivalPlc.

Anyone can help me on my way?

I currently have this statement:

SELECT TripID, Depart, PlaceName, ArrivalPlc, ArrivalTime 
FROM Trips, Places 
WHERE TripID = 'VALUE' AND PlaceName = DepartPlc;

This works as i want it to, but when i add the same thing for ArrivalPlc obviously that does not work...

Upvotes: 1

Views: 64

Answers (3)

Barranka
Barranka

Reputation: 21047

You need to use the places table twice in your from clause:

select 
    tripId, depart, 
    departPlc as departPlaceId, dp.placeName as departPlaceName,
    arrivalPlc as arravalPlaceId, ap.placeName as arrivalPlaceName
from
    trips as t
    inner join places as dp on t.departPlc = dp.placeId
    inner join places as ap on t.arrivalPlc = ap.placeId
where
    t.tripId = 'VALUE`

Notice that I moved the relation conditions from the where clause to the from clause (ussing inner join) for clarity. That way you keep things cleanly separated: the relations and the filters.

Upvotes: 3

Stephen Fischer
Stephen Fischer

Reputation: 2546

You need a second join on Trips

SELECT t.TripID, t.Depart, d.PlaceName AS DepartPlaceName, a.PlaceName AS ArrivalPlaceName, t.ArrivalTime
FROM Trips t, Places d, Places a
WHERE t.TripID = 'VALUE' AND t.DepartPlc = d.PlaceID AND t.ArrivalPlc = a.PlaceID

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52645

Just JOIN Twice using two different table aliases

SELECT DPlaces.placeid D_PlaceID, 
       Aplaces.placeid A_PlaceID 
FROM   trips t 
       INNER JOIN places DPlaces 
               ON t.departplc = DPlaces.placename 
       INNER JOIN places APlaces 
               ON t.arrivalplc = aplaces.placename 
WHERE t.TripID = 'VALUE'

Upvotes: 6

Related Questions