Alex Watts Alex Watts - 6 months ago 10
MySQL Question

"The multi-part identifier could not be bound" with INNER JOIN

I've run into this problem before on another query in the past where I hadn't included the table name ahead of the query like it was expecting, but for today's problem, I'm not sure why it's throwing this error, as the table name is very clearly mentioned before the JOIN happens:

SELECT
TKOPOCs.Name
EightIDs.Email
Cranes.CraneName
FROM
Cranes, EightIDs
INNER JOIN TKOPOCS
ON Cranes.CraneID = TKOPOCs.CraneID
INNER JOIN Mills
ON Cranes.MILLID = Mill.MillID
WHERE EightIDs.EID = TKOPOCs.EID


The exception occurs or Cranes.CraneID and Cranes.MILLID but the items in the SELECT part of the query seem to work fine - it even autofills the column names when I type "Cranes." so it knows perfectly well what the Crane table is and what's in it, so what could be causing the problem?

Answer

JOIN chains are (logically) evaluated - from left to right - before comma separated tables. Include Cranes in the join chain to make it's columns available:

SELECT
  TKOPOCs.Name
  EightIDs.Email
  Cranes.CraneName
FROM
  Cranes
  INNER JOIN TKOPOCS
     ON Cranes.CraneID = TKOPOCs.CraneID
  INNER JOIN Mills
     ON Cranes.MILLID = Mill.MillID
  INNER JOIN EightIDs
     ON EightIDs.EID = TKOPOCs.EID