mysql - Join self-join table with another table -

i have 2 tables: products , meta.

products table:

+----+----------+ | id | name     | +----+----------+ |  1 | tv       | |  2 | computer | |  3 | freezer  | +----+----------+ 

meta table:

+----+------------+-----------+------------+ | id | product_id | meta_key  | meta_value | +----+------------+-----------+------------+ |  1 |          1 | currency  | usd        | |  2 |          1 | price     | 1100       | |  3 |          2 | currency  | pln        | |  4 |          2 | price     | 9300       | |  5 |          3 | currency  | usd        | |  6 |          3 | price     | 1200       | +----+------------+-----------+------------+ 

now following query works fine:

select price.product_id, products.name, price.meta_value 'price', currency.meta_value 'currency'  meta price join meta currency on(price.product_id=currency.product_id , currency.meta_key='currency') join products on(products.id=price.product_id) price.meta_key='price'; 

result:

+------------+----------+-------+----------+ | product_id | name     | price | currency | +------------+----------+-------+----------+ |          1 | tv       | 1100  | usd      | |          2 | computer | 9300  | pln      | |          3 | freezer  | 1200  | usd      | +------------+----------+-------+----------+ 

but query:

select price.product_id, products.name, price.meta_value 'price', currency.meta_value 'currency'  meta price, meta currency join products on(products.id=price.product_id)     price.product_id=currency.product_id     , price.meta_key='price'     , currency.meta_key='currency'; 

returns: "unknown column 'price.product_id' in 'on clause'"

why happen ?

your "from" clause interpreted as:

from meta price, (meta currency join products on (products.id = price.product_id)

so, there no price.product_id available on clause, knows meta currency , products tables.