如何从两个不同的列到单独的表上的同一列进行联接 [英] How to do a JOIN from two different columns to the same column on a separate table

问题描述

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

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.

下面是我正在使用的查询无法正常工作. invTypes上的两个LEFT JOINS导致了此问题.我读了很多书，并且一直在为此绞尽脑汁.我一辈子都想不通如何使该方法返回的名称属于显示在主表中两个单独位置的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.

没有发布整个数据库结构(这是巨大的)的注意事项

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

.每个引用在invTypes表中的同一typeID列. invTypes表包含与所述ID相对应的名称.

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.

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

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

主要问题是，如果从两列中两次引用了invTypes.typeName，我如何1:将表正确连接到这两点，以及2:如何区分两个invTypes中的区别. typeName如果可以创建两个JOIN语句，则返回该值.

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.

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

推荐答案

这里是如何将它们加入一次的方法:

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

这篇关于如何从两个不同的列到单独的表上的同一列进行联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！