Как сделать выборку с двумя внешними ключами одной таблицы на другую таблицу?

Question

[oxaoo]

Допустим, имеются две следующие таблицы:

CREATE TABLE Station
(
	idStation INT NOT NULL AUTO_INCREMENT,
	nameStation CHAR(50) NOT NULL,
	PRIMARY KEY (idStation)
);

CREATE TABLE Ticket
(
	idTrip INT NOT NULL,
	idPassanger INT NOT NULL,
	departureStation INT NOT NULL,
	arrivalStation INT NOT NULL,
	wagon INT NOT NULL,
	place INT NOT NULL,
	PRIMARY KEY (idTrip, idPassanger),
	FOREIGN KEY (idTrip) REFERENCES Trip(idTrip),
	FOREIGN KEY (idPassanger) REFERENCES Passanger(idPassanger),
	FOREIGN KEY (departureStation) REFERENCES Station(idStation),
	FOREIGN KEY (arrivalStation) REFERENCES Station(idStation)
);

Я хочу сделать выборку с выводом названий станций отправления и прибытия, примерно следующего вида:

+-----------+------------+--------------------+------------------+------------+-------+-------+-------+-------------------+-----------------+--------+-------+
| nameTrain | date       | firstname          | lastname         | birthday   | wagon | place | price | departureStation  | arrivalStation  | dep    | arr   |
+-----------+------------+--------------------+------------------+------------+-------+-------+-------+-------------------+-----------------+--------+-------+

Выполняя следующий запрос:

SELECT Train.nameTrain, Trip.time as date, 
Passanger.firstname, Passanger.lastname, Passanger.birthday, 
Price.wagon, Ticket.place, Price.price,
Ticket.departureStation, Ticket.arrivalStation,
Station.nameStation as dep, Station.nameStation as arr
FROM Ticket
JOIN Trip
	ON Ticket.idTrip = Trip.idTrip
JOIN Passanger
	ON Ticket.idPassanger = Passanger.idPassanger
JOIN Train
	ON Trip.idTrain = Train.idTrain
JOIN Price
	ON Train.idTrain = Price.idTrain AND Ticket.wagon = Price.wagon
JOIN Station dst
	ON Ticket.departureStation = dst.idStation AND dep = dst.nameStation
JOIN Station ast
	ON Ticket.arrivalStation = ast.idStation AND arr = ast.nameStation;

Получаю ошибку:

ERROR 1054 (42S22): Unknown column 'Station.nameStation' in 'field list'

А следующий этот запрос:

SELECT Train.nameTrain, Trip.time as date, 
Passanger.firstname, Passanger.lastname, Passanger.birthday, 
Price.wagon, Ticket.place, Price.price,
Ticket.departureStation, Ticket.arrivalStation,
Station.nameStation as dep, Station.nameStation as arr
FROM Ticket
JOIN Trip
	ON Ticket.idTrip = Trip.idTrip
JOIN Passanger
	ON Ticket.idPassanger = Passanger.idPassanger
JOIN Train
	ON Trip.idTrain = Train.idTrain
JOIN Price
	ON Train.idTrain = Price.idTrain AND Ticket.wagon = Price.wagon
JOIN Station
	ON Ticket.departureStation = Station.idStation;

Выдает не совсем нужный результат:

+-----------+------------+--------------------+------------------+------------+-------+-------+-------+------------------+----------------+-----------------------------------+-----------------------------------+
| nameTrain | date       | firstname          | lastname         | birthday   | wagon | place | price | departureStation | arrivalStation | dep                               | arr                               |
+-----------+------------+--------------------+------------------+------------+-------+-------+-------+------------------+----------------+-----------------------------------+-----------------------------------+
| 688Х      | 2016-03-16 | Алик               | Тимофеев         | 1959-09-03 |     6 |    15 |  2107 |             1492 |           2097 | ЗЕЛЕНОГРАДСКАЯ                    | ЗЕЛЕНОГРАДСКАЯ                    |
|...
+-----------+------------+--------------------+------------------+------------+-------+-------+-------+------------------+----------------+-----------------------------------+-----------------------------------+

Answer 1

[nozzy]

SELECT Train.nameTrain, Trip.time as date, 
Passanger.firstname, Passanger.lastname, Passanger.birthday, 
Price.wagon, Ticket.place, Price.price,
Ticket.departureStation, Ticket.arrivalStation,
dst.nameStation as dep, ast.nameStation as arr
FROM Ticket
JOIN Trip
  ON Ticket.idTrip = Trip.idTrip
JOIN Passanger
  ON Ticket.idPassanger = Passanger.idPassanger
JOIN Train
  ON Trip.idTrain = Train.idTrain
JOIN Price
  ON Train.idTrain = Price.idTrain AND Ticket.wagon = Price.wagon
JOIN Station dst
  ON Ticket.departureStation = dst.idStation 
JOIN Station ast
  ON Ticket.arrivalStation = ast.idStation

Answer 2

[Игорь]

Вы ж альясы таблицам присвоили. их и используйте.
замените
Station.nameStation as dep, Station.nameStation as arr
на
dst.nameStation as dep, ast.nameStation as arr

ну и AND dep = dst.nameStation и AND arr = ast.nameStation лишнии

Comments

[oxaoo]

В таком случае: ERROR 1054 (42S22): Unknown column 'dep' in 'on clause'

[Игорь]

oxaoo:
SELECT Train.nameTrain, Trip.time as date,
Passanger.firstname, Passanger.lastname, Passanger.birthday,
Price.wagon, Ticket.place, Price.price,
Ticket.departureStation, Ticket.arrivalStation,
dst.nameStation as dep, ast.nameStation as arr
FROM Ticket
JOIN Trip
ON Ticket.idTrip = Trip.idTrip
JOIN Passanger
ON Ticket.idPassanger = Passanger.idPassanger
JOIN Train
ON Trip.idTrain = Train.idTrain
JOIN Price
ON Train.idTrain = Price.idTrain AND Ticket.wagon = Price.wagon
JOIN Station dst
ON Ticket.departureStation = dst.idStation
JOIN Station ast
ON Ticket.arrivalStation = ast.idStation