...

View Full Version : Too many joins causing unknown column error



bauhsoj
10-08-2011, 04:51 PM
When I try to do the query below I end up with error #1054 - Unknown column 'products.id' in 'on clause'


SELECT *
FROM products,
products_categories
LEFT JOIN categories
ON categories.id = products_categories.id
AND categories.avail = 'Y'
LEFT JOIN category_memberships
ON category_memberships.id = categories.id
AND category_memberships.id IS NULL
LEFT JOIN
(SELECT id,
AVG(vote_value) * COUNT(remote_ip) AS customer_rating
FROM product_votes
GROUP BY id
)
AS product_votes
ON product_votes.id = products.id
WHERE products_categories.id = products.id
AND products_categories.id = 254
AND products_categories.main = 'Y'
AND products.forsale = 'Y'
GROUP BY products.id
ORDER BY customer_rating DESC

I can't seem to figure out an optimal way to create these joins so that they will work as expected and not produce an error.

Can someone help me out with this?

Old Pedant
10-09-2011, 01:37 AM
You can't mix implicit and explicit joins. (Well, you can, under some circumstances, but it's never a good idea.)

So you need to turn that comma between products and products_categories into an INNER JOIN.


...
FROM products INNER JOIN products_categories ON products_categories.id = products.id
...
then remove that condition from the WHERE.

See if that doesn't fix it.

bauhsoj
10-09-2011, 05:23 PM
Tried that. Still not working.

I didn't start getting the error until I added this join into the mix:

LEFT JOIN
(SELECT id,
AVG(vote_value) * COUNT(remote_ip) AS customer_rating
FROM product_votes
GROUP BY id
)
AS product_votes
ON product_votes.id = products.id

Old Pedant
10-09-2011, 11:21 PM
What happens if you re-order the tables?


SELECT *
FROM products
INNER JOIN products_categories
ON products_categories.id = products.id
LEFT JOIN
(SELECT id,
AVG(vote_value) * COUNT(remote_ip) AS customer_rating
FROM product_votes
GROUP BY id
)
AS product_votes
ON product_votes.id = products.id
LEFT JOIN categories
ON categories.id = products_categories.id
AND categories.avail = 'Y'
LEFT JOIN category_memberships
ON category_memberships.id = categories.id
AND category_memberships.id IS NULL
WHERE products_categories.id = 254
AND products_categories.main = 'Y'
AND products.forsale = 'Y'
GROUP BY products.id
ORDER BY customer_rating DESC

Curiosity: Why the LEFT JOIN to categories??? Surely you wouldn't have any records in products_categories that don't have a corresponding record in categories? Isn't products_categories just a many-to-many table?

Are you using INNODB or MyIsam? And what version of MySQL?

Old Pedant
10-09-2011, 11:24 PM
WAIT A MINUTE!

THIS MAKES NO SENSE!


FROM products
INNER JOIN products_categories
ON products_categories.id = products.id
LEFT JOIN categories
ON categories.id = products_categories.id

HUH???

You are using the field products_categories.id to join to *BOTH* the products table *AND* the categories table!

That can't be right!

Maybe you need to show us your schema? Just what fields *ARE* in your products_categories table???

bauhsoj
10-10-2011, 10:32 PM
Curiosity: Why the LEFT JOIN to categories??? Surely you wouldn't have any records in products_categories that don't have a corresponding record in categories? Isn't products_categories just a many-to-many table?

Are you using INNODB or MyIsam? And what version of MySQL?

MyISAM on MySQL 5.0.92.

It is a one-to-one between categories and products_categories. However, products to products_categories is a one-to-many relationship.

Honestly, I've never had to deal with this many joins before and I'm running up against a wall with my understanding of the way they work.

bauhsoj
10-10-2011, 10:44 PM
WAIT A MINUTE!

THIS MAKES NO SENSE!


FROM products
INNER JOIN products_categories
ON products_categories.id = products.id
LEFT JOIN categories
ON categories.id = products_categories.id

HUH???

You are using the field products_categories.id to join to *BOTH* the products table *AND* the categories table!

That can't be right!

Maybe you need to show us your schema? Just what fields *ARE* in your products_categories table???

Each product can be in multiple categories so have multiple entries in the products_categories table. Not all categories are available to public view, so products whose products_categories are in a category that is hidden, aren't allowed to be shown.

The connection is products <-> products_categories <-> categories.

I see where I made in error when I posted the query here. It should look like this:

SELECT *
FROM products
INNER JOIN products_categories
ON products_categories.productid = products.id
LEFT JOIN categories
ON categories.id = products_categories.categoryid
AND categories.avail = 'Y'
LEFT JOIN category_memberships
ON category_memberships.categoryid = categories.id
AND category_memberships.categoryid IS NULL
LEFT JOIN
(SELECT productid,
AVG(vote_value) * COUNT(remote_ip) AS customer_rating
FROM product_votes
GROUP BY productid
)
AS product_votes
ON product_votes.productid = products.id
WHERE products_categories.productid = products.id
AND products_categories.categoryid = 254
AND products_categories.main = 'Y'
AND products.forsale = 'Y'
GROUP BY products.id
ORDER BY customer_rating DESC

Old Pedant
10-10-2011, 10:45 PM
You field names still make no sense.

NORMALLY, I'd expect to see something like this:


Table: products
id int primary key
... etc ...

Table: categories
id int primary key
... etc ...

Table products_categories
product_id int references products(id)
category_id int references categories(id)

If you don't have two *DIFFERENT* id's in products_categories--one that relates to products, one that relates to categories--then your table design is hosed.

It's one reason that I never use "id" alone as a field name.

I would have done:


Table: products
productid int primary key
... etc ...

Table: categories
categoryid int primary key
... etc ...

Table products_categories
productid int references products(productid)
categoryid int references categories(categoryid)

By using names that *TELL* you what the meaning of a field is, it's easier to see mistakes in the JOINs.

Old Pedant
10-10-2011, 10:49 PM
Yes, now the query makes sense

ON categories.id = products_categories.categoryid

That was *exactly* what I was referring and objecting to.

So with that fix you still get the error?

5.0.92 is pretty old. I wonder if you have just run across a bug??

Old Pedant
10-10-2011, 10:50 PM
Now *this* part of the query makes no sense:


LEFT JOIN category_memberships
ON category_memberships.categoryid = categories.id
AND category_memberships.categoryid IS NULL

If category_memberships.categoryid really is NULL, then it can't possibly be equal to categories.id. So you will *never* get any records from category_memberships, so why bother JOINing to it?

FWIW, this is not a complex query. Not even close.

bauhsoj
10-12-2011, 10:37 PM
Now *this* part of the query makes no sense:


LEFT JOIN category_memberships
ON category_memberships.categoryid = categories.id
AND category_memberships.categoryid IS NULL

If category_memberships.categoryid really is NULL, then it can't possibly be equal to categories.id. So you will *never* get any records from category_memberships, so why bother JOINing to it?

FWIW, this is not a complex query. Not even close.

Re-arranging the query and removing the memberships JOIN fixed the problem.

Thanks! :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum