@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 | ЗЕЛЕНОГРАДСКАЯ                    | ЗЕЛЕНОГРАДСКАЯ                    |
|...
+-----------+------------+--------------------+------------------+------------+-------+-------+-------+------------------+----------------+-----------------------------------+-----------------------------------+
  • Вопрос задан
  • 227 просмотров
Решения вопроса 1
@nozzy
Silex, Symfony, Laravel, SQL
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
Ответ написан
Пригласить эксперта
Ответы на вопрос 1
unitby
@unitby
Вы ж альясы таблицам присвоили. их и используйте.
замените
Station.nameStation as dep, Station.nameStation as arr
на
dst.nameStation as dep, ast.nameStation as arr

ну и AND dep = dst.nameStation и AND arr = ast.nameStation лишнии
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы