如何从两个不同的列到另一个表上的同一列进行JOIN

I have two columns on one table that reference the same column on a second table in order to obtain the name that relates to an specified ID.

我在一个表上有两列引用第二个表上的同一列，以获取与指定ID相关的名称。

Below is the query that I am using that is not working. The two LEFT JOINS on the invTypes causes the problem. I have read and read and have been racking my brain on this. I cannot for the life of me figure out how to make this return the names that belong to the IDs that appear in two separate locations in the main table.

以下是我正在使用的查询无效。 invTypes上的两个LEFT JOINS导致问题。我已阅读并阅读，并且一直绞尽脑汁。我不能为我的生活弄清楚如何让它返回属于主表中两个不同位置出现的ID的名称。

THings to note with out posting the entire database structure (which is huge)

需要注意的是发布整个数据库结构（这是巨大的）

both the typeID and itemTypeID are present on the main table. Each of those references the same typeID column in the invTypes table. The invTypes table contains the name that corresponds to said ID.

typeID和itemTypeID都出现在主表上。其中每个引用invTypes表中的相同typeID列。 invTypes表包含与所述ID对应的名称。

Additionally, in the SELECT part of the statement, the typeName comes from the invTypes and the stationName comes from the staStations table.

此外，在语句的SELECT部分中，typeName来自invTypes，而stationName来自staStations表。

The main question is, if the invTypes.typeName is referenced two times from two columns, how do i 1: properly join the tables to those two points and 2: how do i tell the difference in the two invTypes.typeName that is to be returned if the two JOIN statements can be made.

主要问题是，如果从两列引用了两次invTypes.typeName，我该如何1：将表正确地连接到这两个点，以及2：我如何区分两个invTypes.typeName中的差异如果可以生成两个JOIN语句，则返回。

SELECT 
`logTime`,`itemID`,`typeName`,`actorName`,`stationName`,`action`,`passwordType`,
`quantity`,`oldConfiguration`,`newConfiguration` 
        FROM eve_container_audit 
        LEFT JOIN invTypes ON eve_container_audit.typeID = invTypes.typeID
        LEFT JOIN invTypes ON eve_container_audit.itemTypeID = invTypes.typeID

        LEFT JOIN staStations ON eve_container_audit.locationID = staStations.stationID

3 个解决方案

#1

Here's how to join them just once :

以下是如何加入它们一次：

SELECT 
  `logTime`,
  `itemID`,
  `typeName`,
  `actorName`,
  `stationName`,
  `action`,
  `passwordType`,
  `quantity`,
  `oldConfiguration`,
  `newConfiguration` 
FROM
  eve_container_audit 
LEFT JOIN
  invTypes
ON
  eve_container_audit.typeID = invTypes.typeID and
  eve_container_audit.itemTypeID = invTypes.typeID
LEFT JOIN
  staStations
ON
  eve_container_audit.locationID = staStations.stationID

If you need to join them twice, use alias :

如果需要连接两次，请使用别名：

SELECT 
  `logTime`,
  `itemID`,
  `typeName`,
  `actorName`,
  `stationName`,
  `action`,
  `passwordType`,
  `quantity`,
  `oldConfiguration`,
  `newConfiguration` 
FROM
  eve_container_audit 
LEFT JOIN
  invTypes
ON
  eve_container_audit.typeID = invTypes.typeID
LEFT JOIN
  invTypes invTypes2
ON
  eve_container_audit.itemTypeID = invTypes2.typeID
LEFT JOIN
  staStations
ON
  eve_container_audit.locationID = staStations.stationID

#2

Since you are joining the same table twice, you need to give unique aliases to the two copies of the table, or your query will be ambiguous.

由于您要连接两次相同的表，因此需要为表的两个副本提供唯一的别名，否则您的查询将不明确。

...
LEFT JOIN invTypes ON eve_container_audit.typeID = invTypes.typeID
LEFT JOIN invTypes ON eve_container_audit.itemTypeID = invTypes.typeID
...

should instead be something like

应该是类似的东西

...
LEFT JOIN invTypes AS invtypes1 ON
    eve_container_audit.typeID = invtypes1.typeID
LEFT JOIN invTypes AS invtypes2 ON
    eve_container_audit.itemTypeID = invtypes2.typeID
...

#3

Try this. Basically create a second alias of the same table. In the selects refer to inv1 or inv2 specifically to get the right values.

尝试这个。基本上创建同一个表的第二个别名。在选择中，请专门引用inv1或inv2以获取正确的值。

SELECT 
     `logTime`,`itemID`, inv1.`typeName` as main_type , inv2.`typeName` as sub_type, `actorName`,`stationName`,`action`,`passwordType`,
     `quantity`,`oldConfiguration`,`newConfiguration` 
FROM eve_container_audit 
      LEFT JOIN invTypes as inv1 ON eve_container_audit.typeID = inv1.typeID
      LEFT JOIN invTypes as inv2 ON eve_container_audit.itemTypeID = inv2.typeID
      LEFT JOIN staStations ON eve_container_audit.locationID = staStations.stationID

#1