CODEWITHSUNDEEP
mysqlsql
kfirba
kfirba

Reputation: 5441

SQL query is producing an error which I can't solve

I've got the following tables which im querying:

categories
  -id
  -name
  -parent_id
  -last

products2categories
  -id
  -product_id
  -category_id

What im trying to do is to get the record's category name and category id which the product_id in the table products2categories is 10 and last in the table categories is 1

this is the query that i've tried to execute:

SELECT `categories.id`,`categories.name` 
FROM `categories` 
JOIN `products2categories` 
ON `categories.id` = `products2categories.category_id`
WHERE `categories.last` = 1 
AND `products2categories.product_id`= 10

I've been returned with the following error:

#1054 - Unknown column 'categories.last' in 'where clause'

To demonstrate what I'm trying to get:

categories
  -id          8
  -name        "my_name"
  -parent_id   0
  -last        1

products2categories
  -id          1
  -product_id  10
  -category_id 1

I want to get as a result the ID of 8 and the name of "my_name" but it has to be based on the table products2categories, cause one product, can have more than one category which it's related to.

Thanks in advance!

Upvotes: 0

Views: 76

Answers (4)

Kai
Kai

Reputation: 2168

You are delimiting fields incorrectly. Delimit the table name and column name separately:

SELECT `categories`.`id`,`categories`.`name` 
FROM `categories` 
JOIN `products2categories` 
ON `categories`.`id` = `products2categories`.`category_id`
WHERE `categories`.`last` = 1 
AND `products2categories`.`product_id`= 10

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116110

Use the backticks around a single identifier:

So

`categories.last`

should be

`categories`.`last`

because the table name and the column name are separate identifiers.

In this case, you don't need the backticks at all, by the way, so it should work fine if you remove them, like this:

categories.last

You need the backticks only for column names containing spaces or some other special characters, and maybe for names that are equal to some reserved words in some circumstances. But as far as I can tell, you don't need them at all in your case, and I don't recall every having had to use them.

Upvotes: 1

Sylvain Leroux
Sylvain Leroux

Reputation: 52000

A quick check of your syntax: categories.last shouldn't be categories.last instead?

Do as same for all other "fields"

Upvotes: 1

juergen d
juergen d

Reputation: 204766

Replace

WHERE `categories.last` = 1

with

WHERE `categories`.`last` = 1

Backticks for escaping column and table names can't be used to escape the combination of table and column. It must be applyed on both.

Upvotes: 4

Related Questions