CODEWITHSUNDEEP
mysqlmysql-error-1054
Chris Mitchell
Chris Mitchell

Reputation: 3

INNER JOIN issue

I am getting the following error from this query:

1054 - Unknown column 'sheet_items.color_id' in 'on clause'

select sheet_items.sheet_id, sheet_items.quantity, materials.material, colors.color
from sheet_items, materials
inner join colors
on colors.color_id=sheet_items.color_id
where sheet_items.sheet_id=4 and sheet_items.material_id=materials.material_id

Any ideas? Thanks in advance!

Upvotes: 0

Views: 81

Answers (4)

Costlow
Costlow

Reputation: 585

Don't mix up the way you do JOINs, it gets very confusing.

SELECT sheet_items.sheet_id, sheet_items.quantity, materials.material, colors.color
FROM
sheet_items
INNER JOIN materials ON materials.id=sheet_items.material_id
INNER JOIN colors ON colors.color_id=sheet_items.color_id
WHERE
sheet_items.sheet_id=4

Upvotes: 0

Brian Driscoll
Brian Driscoll

Reputation: 19635

You have an implicit inner join in your where clause between sheet_items and materials. You should take that out of your where clause and put it into your projection, so that it looks like this:

select sheet_items.sheet_id, sheet_items.quantity, materials.material, colors.color from materials
inner join sheet_items on materials.material_id=sheet_items.material_id 
inner join colors on colors.color_id=sheet_items.color_id 
where sheet_items.sheet_id=4

Upvotes: 0

Rich Andrews
Rich Andrews

Reputation: 4188

Looks like your mixing pre & post ANSI92 SQL, try either...

PRE ANSI92

select sheet_items.sheet_id, sheet_items.quantity, materials.material, colors.color
from sheet_items, materials, colors
where sheet_items.sheet_id=4 and sheet_items.material_id=materials.material_id AND
colors.color_id=sheet_items.color_id

POST ANSI92

select sheet_items.sheet_id, sheet_items.quantity, materials.material, colors.color
from sheet_items
inner join materials
on sheet_items.material_id=materials.material_id
inner join colors
on colors.color_id=sheet_items.color_id
where sheet_items.sheet_id=4

Either way IMHO the second format is easier to read/understand & debug and will work across all SQL platforms

Upvotes: 1

Stefan H
Stefan H

Reputation: 6683

The way you have it structured now, you are trying to inner join Materials with colors, therefore, it does not know what column you are talking about.. try

select sheet_items.sheet_id, sheet_items.quantity, materials.material, colors.color
from materials,sheet_items
inner join colors
on colors.color_id=sheet_items.color_id
where sheet_items.sheet_id=4 and sheet_items.material_id=materials.material_id

Upvotes: 1

Related Questions