Thread

Hi,

So this is a bit off topic and more of a question than anything else but I’ve been struggling over this SQL problem all day and wondered if anybody out there could offer a quick bit of advice! Is there anything wrong with this code?

CREATE PROCEDURE `catalog_get_product_locations`(IN inProductId INT)
BEGIN SELECT aa.cat_id, aa.cat_name AS cat_name, aa.dept_id AS dept_name
FROM cb_category aa
INNER JOIN cb_department bb ON bb.dept_id = aa.dept_id
WHERE aa.cat_id
IN (
SELECT cat_id
FROM cb_product_category
WHERE prod_id = inProductId
);

END$$

It’s a stored procedure but when I serve it up I get the following error:

Column not found: 1054 Unknown column ‘aa.dept_id’ in ‘field list’

Surely the table join should mean the cb_department dept_id row is linked to the cb_category table?

Any help would be greatly appreciated!

Thanks,

Steve

Wild guess.

FROM cb_category aa

Should be:

FROM cb_category AS aa

And likewise on the joined table.

Jamie

Perhaps dept_name is indeed not a column of aa, is it perhaps a column of bb? 😊

Hi Jamie / Daniel,

Thanks for you recommendations, unfortunately I just couldn’t get it to work so ended up rewriting the query!

CREATE PROCEDURE `catalog_get_product_locations`(IN inProductId INT)
BEGIN  
SELECT c.cat_id, c.cat_name, d.dept_id, d.dept_name
FROM cb_category c
INNER JOIN cb_category_department cd ON c.cat_id = cd.cat_id
INNER JOIN cb_department d ON cd.dept_id = d.dept_id
INNER JOIN cb_product_category pc ON c.cat_id = pc.cat_id
WHERE prod_id = inProductId;

END

Seems to be working okay now (fingers crossed)!

Steve