php - MySQL query, add new alias -

i have mysql query using search script , need add field (like spv.term alias) code tell me error number: 1054 unknown column 's.id' in 'on clause'.

$query = " select   sp.url,   sp.id,   s.name,   sp.hit,   sp.hot,   sp.action,   sp.id,   sp.smallimage,   sp.mainmodimage,   (select      stock    shop_product_variants    shop_product_variants.product_id = s.id        , stock > 0    limit 1) stock,   (select      price    shop_product_variants    shop_product_variants.product_id = s.id     or shop_product_variants.product_id = s.id        , stock = 0    limit 1) price,   (select      id    shop_product_variants    shop_product_variants.product_id = s.id     or shop_product_variants.product_id = s.id        , stock = 0    limit 1) v_id,   (select      old_price    shop_product_variants    shop_product_variants.product_id = s.id        , stock > 0    limit 1) old_price shop_products_i18n s,   shop_product_variants spv   inner join shop_products sp     on sp.id = s.id sp.active = 1     , s.name '%" . $get . "%'      or sp.url '%" . $get . "%'      or spv.term '%" . $get . "%' group s.id order stock desc "; 

this from clause:

from shop_products_i18n s,      shop_product_variants spv inner join      shop_products sp      on sp.id = s.id 

the problem mixing old-style joins , new-style joins. simple rule: never use commas in from clause.

i think mean:

from shop_products_i18n s inner join      shop_products sp      on sp.id = s.id 

because don't use spv anywhere else in outer query, except where clause. condition should go each of subqueries.

by way, can fix original problem replacing , cross join:

from shop_products_i18n s cross join      shop_product_variants spv inner join      shop_products sp      on sp.id = s.id 

although , , cross join both perform cartesian products, behave differently in from clause in terms of precedence. problem have , precedence rules following inner join parsed first -- columns in s not available.