SQL Query, Unknown column 'p.prod_id' in 'on clause'

jkirkerx

I'm upgrading a PHP website that uses MySQL to use PDO, pretty big project but I have this one SQL Query that won't play ball with me. I'm no expert in SQL Queries, my biggest weakness, but I'm just easter egg hunting on this one. I get this error Unknown column 'p.prod_id' in 'on clause' The script works on the customers production system, but it has never worked on my development system that I had to build to work on the project. It was originally written for the original mysql handle or object. I forget what version of MySQL they are using, but I'm using the lastest windows version. I'm Interested in you just looking at it, and if you see or know something, then perhaps you can point me in the right direction. I came to the conclusion that perhaps its poorly written, and somehow it worked, but in PDO, which is more strict, it won't fly.

SELECT DISTINCT p.prod_id, p.model, p.showpricing, p.image_file, p.image_width, p.image_height, p.image_border, p.name, m.manufacturer,
CASE WHEN sp.price IS NULL THEN 0 ELSE sp.price END AS list_price,
CASE WHEN sp.price IS NULL OR ssp.discount IS NULL THEN 0 ELSE sp.price - ssp.discount END AS sale_price,
CASE WHEN NOW() BETWEEN p.mfgrebatestart AND p.mfgrebateend THEN p.mfgrebate ELSE 0 END AS mfgrebate

FROM comm_product p, comm_manufacturer m

INNER JOIN comm_dept_prod dp ON p.prod_id = dp.prod_id
INNER JOIN comm_partners_products pa ON p.prod_id = pa.prod_id
LEFT JOIN comm_sku_price sp ON p.default_sku_id = sp.sku_id AND NOW()

BETWEEN sp.date_start AND sp.date_end
LEFT JOIN comm_sku_sale_price ssp ON p.default_sku_id = ssp.sku_id AND NOW()
BETWEEN ssp.date_start AND ssp.date_end

WHERE pa.p_id = :partnerID
AND p.mfg_id = m.mfg_id
AND p.avail_type < 3 AND p.image_file > ''
AND p.model NOT LIKE 'EBAY-%'
AND p.dept_ad = 1
AND dp.dept_id = :deptID

ORDER BY rand() LIMIT 4

comm_product, I copied the columns from mysql workbench

table comm_product
`prod_id`, `model`, `name`, `list_order`,
`image_file`, `image_width`, `image_height`, `image_border`,
`big_image_file`, `big_image_width`, `big_image_height`, `big_image_border`,
`feature_image_file`, `mfgrebate`, `mfgrebatestart`, `mfgrebateend`,
`taxable`, `comp_name`, `comp_price`, `item_desc`,
`attr_label1`, `attr_label2`, `attr_label3`, `attr_label4`,
'attr_label5`, `special_note`, `manager_note`, `page_id`,
`pdf_file`, `edittrack`, `date_created`, `time_created`,
`user_created`, `date_edited`, `

Tim Carmichael

First guess.. does prod_id exist in comm_product? Since p.prod_id is being referenced and p is the alias for comm_product, I'd start there.

jkirkerx

Thanks for looking at it. I did check that. I went ahead and edited my post and added that table to it for inspection. I'll probably need to add all the tables.

Jorgen Andersson

What I can see is a mixture of ANSI joins and an implicit join. And I can understand if the optimizer pukes on that. Try using all ANSI Joins:

SELECT DISTINCT p.prod_id, p.model, p.showpricing, p.image_file, p.image_width, p.image_height, p.image_border, p.name, m.manufacturer,
CASE WHEN sp.price IS NULL THEN 0 ELSE sp.price END AS list_price,
CASE WHEN sp.price IS NULL OR ssp.discount IS NULL THEN 0 ELSE sp.price - ssp.discount END AS sale_price,
CASE WHEN NOW() BETWEEN p.mfgrebatestart AND p.mfgrebateend THEN p.mfgrebate ELSE 0 END AS mfgrebate

FROM comm_product p
INNER JOIN comm_manufacturer m ON p.mfg_id = m.mfg_id
INNER JOIN comm_dept_prod dp ON p.prod_id = dp.prod_id
INNER JOIN comm_partners_products pa ON p.prod_id = pa.prod_id
LEFT JOIN comm_sku_price sp ON p.default_sku_id = sp.sku_id AND NOW()

BETWEEN sp.date_start AND sp.date_end
LEFT JOIN comm_sku_sale_price ssp ON p.default_sku_id = ssp.sku_id AND NOW()
BETWEEN ssp.date_start AND ssp.date_end

WHERE pa.p_id = :partnerID

AND p.avail_type < 3 AND p.image_file > ''
AND p.model NOT LIKE 'EBAY-%'
AND p.dept_ad = 1
AND dp.dept_id = :deptID

ORDER BY rand() LIMIT 4

Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers. Buckminster Fuller

jkirkerx

Wow thanks. I ran it in the mysql workbence. it didn't produce an error and ran clean, but it didn't product any rows on :partner = 1000 and :dept = 52. But it's a step closer. Oh wait, changed the dept to 54, and got rows! Cool. Let me give it a test run in the code, should work fine. I may have another one today This was way over my head here. It's hard to be a programmer, art guy, HTML and CSS all at once. The SQL stuff seems to be an expertise on a higher level. Thanks Jorgen, your the best! I don't understand the ANSI JOIN versus the implicit, guess I can Google that to learn exactly what was wrong.

Jorgen Andersson

jkirkerx wrote:

Wow thanks

My pleasure.

jkirkerx wrote:

I don't understand the ANSI JOIN versus the implicit, guess I can Google that to learn exactly what was wrong.

Yes, or even better, in this case, Wikipedia[^].

Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers. Buckminster Fuller

jkirkerx

I plugged it in, pretty sure I got the translation right because I don't get a SQL error and the SQL Query matches when I print it out on the screen. It's really close, but I guess it's suppose to produce a result set of 4 rows on almost every dept ID This is out of my league here. oh well. Thanks for help, At least I know my code changes work, and I can do testing with it.