mysql inner join statement error

Question

I am getting the following error:

1052 - Column 'product_id' in field list is ambiguous

When I run the following:

SELECT `product_id`, `product_name` FROM `products` INNER JOIN `products_has_product_category` ON `products.product_id` = `products_has_product_category.product_id` AND `products_has_product_category.category_id` = 1 ORDER BY `products.product_name`

My PRODUCTS table has

product_id, product_name, etc

My products_has_product_category table has

product_id, category_id

This is my first try at a join, so I appreciate the help!

Taryn · Accepted Answer · 2012-10-09 22:24:09Z

You need to specify which table the product_id comes from. Since the product_id is in both tables, when you SELECT it you need to specify which table you want the value from. With a table alias:

SELECT p.product_id, p.product_name FROM `products` p INNER JOIN `products_has_product_category` pc ON p.product_id = pc.product_id AND pc.category_id = 1 ORDER BY p.product_name

Without table aliases:

SELECT `products`.`product_id`, `products`.`product_name` FROM `products` INNER JOIN `products_has_product_category` ON `products.product_id` = `products_has_product_category.product_id` AND `products_has_product_category.category_id` = 1 ORDER BY `products.product_name`

blearn · Accepted Answer · 2012-10-09 22:41:54Z

If I understand your intention correctly, you probably meant WHERE rather than AND:

SELECT `products`.`product_id`, `products`.`product_name` FROM `products` INNER JOIN `products_has_product_category` ON `products.product_id` = `products_has_product_category.product_id` WHERE `products_has_product_category.category_id` = 1 ORDER BY `products.product_name`

Let me rephrase that - WHERE instead of AND. Joins are performed on relationships between 2 tables, not between a value and a column in a table. Where clauses are used for the latter situation.
Sorry, I miss understood what you had said, but bluefeet's answer is what I was looking for. I did test your answer, and I still got the 1052 - Column 'product_id' in field list is ambiguous error.

Collectives™ on Stack Overflow

mysql inner join statement error

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related