0

I am doing some work on a MySQL database and completely stumped on receiving the error

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

Both the table and the column names exist and are correct. I just created it in 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`
devlin carnate
  • 8,309
  • 7
  • 48
  • 82
Matt Searles
  • 2,656
  • 2
  • 16
  • 18
  • 2
    Could you add some details about the table structures/definitions of the joined tables? – Jon Limjap Nov 18 '09 at 05:32
  • what happens when you start building the query with only the table 'Puzzles' and then add the additional tables one after the other? Does that work? – lexu Nov 18 '09 at 05:39

2 Answers2

2

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
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Parenthesizing your joins is a bit of a sanity check, and doesn't mysql give you a better error message showing where the parse broke? – Don Nov 18 '09 at 06:33
  • Thanks for the explanation Bill. Until yesterday I'd never heard of comma-style and SQL-92 style joins. – Matt Searles Nov 19 '09 at 23:37
1

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.

Bell
  • 17,907
  • 4
  • 23
  • 25