php - 您的 SQL 语法有误；查看手册

Question

您的 SQL 语法有误；查看与您的 MySQL 服务器版本对应的手册，了解在第 4 行的“WHERE j.id_customer = 1”附近使用的正确语法

SELECT j.`id_customer`, j.`id_order`, m.`id_shop`
        FROM `ps_orders` j     
        LEFT JOIN `ps_order_detail` m   
        WHERE j.`id_customer` = 1

如果打开了详细的纠错，这是从 prestashop php 中的原始代码生成的 ------

 foreach ($result as $key)
        {
                        $customer_id_is = 1;
             $product_id_is = 5;
                    $result2 = Db::getInstance()->executeS(
        'SELECT j.`id_customer`, j.`id_order`, m.`id_shop`
        FROM `'._DB_PREFIX_.'orders` j     
        LEFT JOIN `'._DB_PREFIX_.'order_detail` m   
        WHERE j.`id_customer` = '.$customer_id_is.'


          ');  

        }

Answer 1

您的 JOIN 中缺少 ON 子句

SELECT j.id_customer, j.id_order, m.id_shop FROM ps_orders j
LEFT JOIN ps_order_detail m
    ON m.SomeField = j.SomeField <-- HERE 
WHERE j.id_customer = 1

如果您要加入您的表格，您需要在一个字段上链接它们。否则，您正在执行交叉连接。

MySQL 文档状态:

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.

所以这会起作用:

SELECT j.id_customer, j.id_order, m.id_shop FROM ps_orders j
JOIN ps_order_detail m
WHERE j.id_customer = 1

就像这样:

SELECT j.id_customer, j.id_order, m.id_shop FROM ps_orders j
INNER JOIN ps_order_detail m
WHERE j.id_customer = 1

但它不适用于 OUTER JOIN。