mysql join not working giving error

Question

values table

id  name    attributes_id
1   black   3 [->]
2   red     3 [->]
3   blue    3 [->]
5   tortorise   3 [->]
6   oakley  1 [->]
9   green   3 [->]
10  native  1 [->]

product_values table

values_id   products_id
1 [->]  5 [->]
2 [->]  10 [->]

sql

SELECT values.name, product_values.products_id  FROM `product_values`  inner 
join values ON product_values.values_id=values.id;

error

You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'values on 
product_values.values_id=values.id' at line 1

thanks for any assistance.

score 3 · Accepted Answer · answered Apr 15 '13 at 14:36

3

You should probably put some quotes around the values table reference. It's a reserved word

SELECT
    `values`.name,
    product_values.products_id 
FROM
    product_values
    INNER JOIN `values` ON product_values.values_id = `values`.id;

answered Apr 15 '13 at 14:36

Kevin Brydon

12,524
8
46
76

I feel silly now i will change the name of the table now. I did not think it would let me name a table a reserved word. – John Hutchinson Apr 15 '13 at 14:46

score 0 · Answer 2 · answered Apr 15 '13 at 14:40

According to the manual, VALUES is a reserved keyword of mysql. see here: MySQL Reserved Keywords List. In order to avoid getting syntax error, the keyword or column name should be escape using this symbol called backtick. Example,

SELECT values.name, product_values.products_id  
FROM   `product_values`  
        inner join `values` ON product_values.values_id = `values`.id;

mysql join not working giving error

2 Answers2