如何在不同的地方向表添加两次相同的列？

My db tables are like that:

我的db表是这样的:

routes - table for routes, with columns:
a. destination_id
b. departure_id

routes - 路由表,包含列:a。 destination_id b。 departure_id

destinations - table for all the destinations, with columns:
a. destination_id
b. destination_name.

destination - 所有目标的表,包含列:a。 destination_id b。 DESTINATION_NAME。

Because every destination is also departure point I use one table for both.

因为每个目的地也是出发点我两个都使用一个表。

Relation is as follows :

1. routes.destination_id = destinations.destination_id,
also
2. routes.departure_id = destinations.destination_id

关系如下:1. routes.destination_id = destinations.destination_id,也是2. routes.departure_id = destinations.destination_id

The result I need is as described:

我需要的结果如下所述:

If routes.destination_id = 3 and routes.departure_id = 5, I want to match each of them to the corresponds name in the destinations table, and to put in the result the correct column names, for departure I want to call it departure_city and same for destination. Right now I get the same name for both different ids, so if destination_id #3 is New York and destination_id #5 is Las Vegas, I will get both as New York and that is not what I need.

如果routes.destination_id = 3和routes.departure_id = 5,我想将它们中的每一个与目的地表中的对应名称相匹配,并在结果中输入正确的列名,对于出发我想称之为departure_city和相同目的地。现在我得到了两个不同ID的相同名称,所以如果destination_id#3是纽约而destination_id#5是拉斯维加斯,那么我将同时获得纽约,这不是我需要的。

So far, my query looks like that:

到目前为止,我的查询看起来像这样:

SELECT routes.*, destinations.destination_name as 'departure_city', destinations.destination_name as 'destination_city'
FROM routes, destinations
WHERE routes.route_departure_id = destinations.destination_id

Before you are telling me to use JOIN, I have done it with JOIN but couldn't change the column name! Also, I always received all the table's columns and I do not know how to get only necessary columns with JOIN!

在你告诉我使用JOIN之前,我已经使用JOIN完成了但是无法更改列名!此外,我总是收到所有表的列,我不知道如何只用JOIN获得必要的列!

Right now my issues are that I can't get the value from destinations twice, once for the route_destination and one for the route_departure and show different values.

现在我的问题是我无法从目的地获取两次值,一次是route_destination,另一次是route_departure,并显示不同的值。

1 个解决方案

#1

You are looking for two joins:

您正在寻找两个连接:

SELECT r.*, 
       dep.destination_name as departure_city, 
       dest.destination_name as destination_city
FROM routes r JOIN
     destinations dep
     ON r.departure_id = dep.destination_id JOIN
     destinations dest
     ON r.destination_id = dest.destination_id;

Additional advice:

Use table aliases. They make the query easier to write and to read.

使用表别名。它们使查询更容易编写和阅读。

Never use commas in the FROM clause. Always use explicit JOIN syntax.

切勿在FROM子句中使用逗号。始终使用显式JOIN语法。

Do not use single quotes to define column aliases. Only use single quotes for string and date constants. Use backticks if you are using a name that needs to be quoted (and then, change the name so backticks aren't necessary).

不要使用单引号来定义列别名。仅对字符串和日期常量使用单引号。如果您使用需要引用的名称,请使用反引号(然后,更改名称,以便不需要反引号)。

#1