Trying to join multiple tables in mysql – get error 1054, Unknown Column

I want to join multiple tables using JOINs, and mysql/mariadb is refusing to find one column. The column exists, of course, and I can’t figure out what the cause might be.

Table Layout

CREATE TABLE `shops` (
  `id` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
);

CREATE TABLE `shops2categories` (
  `shopid` int(11) NOT NULL,
  `categoryid` int(11) NOT NULL,
);

CREATE TABLE `categories` (
  `id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
);

CREATE TABLE `categories2items` (
  `itemid` int(11) NOT NULL,
  `categoryid` int(11) NOT NULL
);

CREATE TABLE `items` (
  `id` int(11) NOT NULL,
  `name` varchar(32) NOT NULL,
);

Query: In order to avoid confusion with aliases, I now ran the query with the original table names.

SELECT
shops.name,
categories.name,
items.name

FROM shops

LEFT JOIN shops2categories ON shops2categories.shopid = shops.id
LEFT JOIN categories2items ON categories2items.categoryid = categories.id

LEFT JOIN categories ON categories.id = shops2categories.categoryid
LEFT JOIN items ON items.id = categories2items.itemid

Error Message:

#1054 - Unknown column 'categories.id' in 'on clause'

No matter how I restructured my query (foreign key first, primary key first, items table first, categories table first, …, using different JOIN types), I can’t seem to get this to work. Also I read through a whole lot of SO questions to this topic, but I feel that the order is correct, I am not inserting nor updating, and it’s not about quoting. Something is fundamentally broken in my concept, and I’m very keen on learning what this could be.

Answer

Look at your from clause. You reference c.id before you have defined c.

A table cannot be referenced until it is defined in the FROM clause.

You would seem to want:

FROM shops s LEFT JOIN
     shops2categories s2c
     ON s2c.shopid = s.id LEFT JOIN
     categories c
     ON c.id = s2c.categoryid LEFT JOIN
     categories2items c2i
     ON c2i.categoryid = c.id LEFT JOIN
     items i
     ON i.id = c2i.itemid

Advertisement

Answer