Unknown column {0} in on clause

Question

Hi,

I'm doing some work on a MySQL database... and I'm completely stumped as to why I'm receiving the error

1054 - Unknown column 'Puzzles.PuzzleID' in 'on clause'

Both the table and the column names exist and are correct... I mean, I just created it Navicat's visual designer...

SELECT
`PuzzleCategories`.`PuzzleCategory`,
`Clients`.`BusinessName`,
`Puzzles`.`PuzzleNumber`
FROM
`Puzzles`
Inner Join `PuzzleCategories` ON `Puzzles`.`PuzzleCategoryID` = `PuzzleCategories`.`PuzzleCategoryID` ,
`Clients`
Inner Join `Publications` ON `Clients`.`ClientID` = `Publications`.`ClientID`
Inner Join `PublicationIssues` ON `PublicationIssues`.`PublicationID` =     `Publications`.`PublicationID`
Inner Join `PuzzleUsages` ON `PuzzleUsages`.`PuzzleID` = `Puzzles`.`PuzzleID` AND `PuzzleUsages`.`PublicationIssueID` = `PublicationIssues`.`PublicationIssueID`

Any help would be massively appreciated.

Warm regards, Matt

Answer 1

I'd guess Puzzles doesn't have a column PuzzleID. Is the column called simply ID in that table? Or Puzzle_ID?

You should run SHOW CREATE TABLE Puzzles to see the current definition of that table.

Sometimes a missing quote can be the culprit:

... ON `Puzzles.PuzzleID` ...

The above would look for a column literally named "Puzzles.PuzzleID," that is, a column name 16 characters long with a dot in the middle.

---

@Bell deserves the prize for noticing that you're mixing comma-style joins and SQL-92 style joins. I didn't notice that!

You shouldn't use both in the same query, because the precedence of join operations is probably causing the confusion.

The JOIN keyword has higher precedence. Simplifying your query so we can look at the table-expressions, it would be evaluated as follows:

SELECT . . . 
FROM (Puzzles JOIN PuzzleCategories),
(Clients JOIN Publications JOIN PublicationIssues JOIN PuzzleUsages)

The problem is that the join to PuzzleUsages needs to compare to the Puzzles.PuzzleID column, but because of the precedence issue, it can't. The column is not part of the operands of the last JOIN.

You can use parentheses to resolve the error, explicitly overriding precedence of table-expressions (just as you would use parentheses in arithmetic expressions):

SELECT . . . 
FROM Puzzles JOIN (PuzzleCategories, Clients)
JOIN Publications JOIN PublicationIssues JOIN PuzzleUsages

Or you can just use SQL-92 JOIN syntax consistently. I agree with @Bell that this is more clear.

SELECT . . . 
FROM Puzzles JOIN PuzzleCategories JOIN Clients
JOIN Publications JOIN PublicationIssues JOIN PuzzleUsages

Answer 2

If you're sure that the column names are right the problem may be from the order of the joins. It sounds like the joins each side of the comma are being built separately. (I'm not sure if this is likely or even possible but it's the only guess I have based on the info you give)

The query could be restructured as:

SELECT
`PuzzleCategories`.`PuzzleCategory`,
`Clients`.`BusinessName`,
`Puzzles`.`PuzzleNumber`
FROM
`Clients`
INNER JOIN `Publications` ON `Clients`.`ClientID` = `Publications`.`ClientID`
INNER JOIN `PublicationIssues` ON `PublicationIssues`.`PublicationID` =   `Publications`.`PublicationID`
INNER JOIN `PuzzleUsages` ON`PuzzleUsages`.`PublicationIssueID` = `PublicationIssues`.`PublicationIssueID`
INNER JOIN `Puzzles` ON `Puzzles`.`PuzzleID` = `PuzzleUsages`.`PuzzleID`
INNER JOIN `PuzzleCategories` ON `Puzzles`.`PuzzleCategoryID` = `PuzzleCategories`.`PuzzleCategoryID`

which just reads better anyway.