I have two tables:
ID | Place1 | Time1 | Place2 | Time2 | Place3 | Time3 |
1 | 1 | 10 | 2 | 23 | 1 | 21 |
2 | 2 | 5 | 3 | 4 | 1 | 12 |
ID | PlaceName |
1 | Austria |
2 | Germany |
3 | India |
I want do a WHERE tblItinerary = 1 to see the result set as:
Place1 | Place2 | Place3 |
Austria | Germany | Austria |
Right now the direction I have is something like
SELECT tblPlaces.PlaceName
FROM tblPlaces
JOIN tblPlaces.PlaceName
ON tblItinerary.Place1 = tblPlaces.PlaceName
JOIN tblPlaces.PlaceName
ON tblItinerary.Place2 = tblPlaces.PlaceName
JOIN tblPlaces.PlaceName
ON tblItinerary.Place3 = tblPlaces.PlaceName
WHERE tblItinerary.ID = 1;
The idea is correct, but there are some mistakes: to join one table multiple times, you should use unique aliases for each table. Also, you have not actually joined tblItinerary anywhere and your join syntax is incorrect (your attempt to join tblPlaces.PlaceName instead of tblPlaces). Try the following:
select p1.PlaceName as Place1
, p2.PlaceName as Place2
, p3.PlaceName as Place3
from tblItinerary i
join tblPlaces p1 on i.Place1 = p1.ID
join tblPlaces p2 on i.Place2 = p2.ID
join tblPlaces p3 on i.Place3 = p3.ID
where i.ID = 1;
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments