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.
Comments
Post a Comment