CODEWITHSUNDEEP
mysqlsqljoin
Wes
Wes

Reputation: 281

How to do a JOIN from two different columns to the same column on a separate table

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.

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)

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.

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

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

Upvotes: 1

Views: 3067

Answers (3)

Sabeen Malik
Sabeen Malik

Reputation: 10880

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.

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

Upvotes: 1

Hammerite
Hammerite

Reputation: 22340

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
...

Upvotes: 1

Hendra Jaya
Hendra Jaya

Reputation: 1628

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

Upvotes: 1

Related Questions