Link to home
Start Free TrialLog in
Avatar of bitt3n
bitt3n

asked on

why does this MySQL query fail?

The following query fails in MySQL 5.1.56:

	SELECT 
	shop_id, products.product_id AS
	product_id, brand, title, price, image, image_width, image_height
	
	FROM products, users LEFT JOIN
	
	(
		SELECT fav5.product_id AS product_id, SUM(CASE 
		WHEN fav5.current = 1 AND fav5.closeted = 1 THEN 1
		WHEN fav5.current = 1 AND fav5.closeted = 0 THEN -1
		ELSE 0
		END) AS favorites_count
		FROM favorites fav5
		GROUP BY fav5.product_id 
	
	) AS fav6 ON products.product_id=fav6.product_id
WHERE products.product_id= 46876 AND users.user_id!=products.product_id

Open in new window


The error is

#1054 - Unknown column 'products.product_id' in 'on clause'

Open in new window


This modification without the user table does not fail:

	SELECT 
	shop_id, products.product_id AS
	product_id, brand, title, price, image, image_width, image_height
	
	FROM products LEFT JOIN
	
	(
		SELECT fav5.product_id AS product_id, SUM(CASE 
		WHEN fav5.current = 1 AND fav5.closeted = 1 THEN 1
		WHEN fav5.current = 1 AND fav5.closeted = 0 THEN -1
		ELSE 0
		END) AS favorites_count
		FROM favorites fav5
		GROUP BY fav5.product_id 
	
	) AS fav6 ON products.product_id=fav6.product_id
WHERE products.product_id= 46876

Open in new window


Neither query fails in MySQL 5.0.67. (I exported the database from 5.0.67 and imported into 5.1.56 so the structure should be identical.)

The products table does have a product_id column of type int(10). The favorites table also has a product_id column of type int(10). What is going on?
ASKER CERTIFIED SOLUTION
Avatar of bitt3n
bitt3n

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial