CODEWITHSUNDEEP
mysqlinner-join
Byron Claiborne
Byron Claiborne

Reputation: 215

mySQL 'invalid column' error

I'm trying to insert a relationship in table 'products_to_categories' when the condition is met in the 'products' table (master_categories_id = 500) My guess is because my SELECT statement should be a JOIN? 

table: products_to_categories
columns: products_id, categories_id

table: products
columns: products_id, master_categories_id

INSERT INTO products_to_categories (products_id, categories_id)  
SELECT products.products_id, 500 FROM products  
WHERE products.master_categories_id=500 HAVING count(categories_id) < 1;

error returned: categories_id is an invalid column

Upvotes: 0

Views: 141

Answers (2)

James Cronen
James Cronen

Reputation: 5763

The categories_id column needs to be available in one of the tables in your SELECT. In this case, only products is there, so the only valid columns are products_id or master_categories_id.

Upvotes: 1

Jake Toolson
Jake Toolson

Reputation: 480

few ways:

SELECT products.products_id, 500, products.categories_id FROM products
WHERE products.master_categories_id=500 HAVING count(categories_id) < 1;

Or

SELECT products.products_id, 500, count(products.categories_id) as cat_count FROM products
WHERE products.master_categories_id=500 HAVING cat_count < 1;

Upvotes: 1

Related Questions