mysql - 在 Mariadb 中向 3 表查询添加进一步的条件

Question

当前查询

SELECT vcc.name AS item, vcc.prodid, vcc.quantity AS qty,
       UPPER(vcc.custom_message) AS nickname, vfd1.value AS fullname,
       vfd2.value as email, vcc.purchaseid, vcc.price
FROM vxu_4_wpsc_cart_contents AS vcc INNER JOIN
     vxu_4_wpsc_submited_form_data AS vfd1
     ON vcc.purchaseid = vfd1.log_id AND vfd1.form_id = 2 INNER JOIN
     vxu_4_wpsc_submited_form_data AS vfd2
     ON vcc.purchaseid = vfd2.log_id AND vfd2.form_id = 9

我想添加一个条件，即表“vxu_4_wpsc_purchase_logs”上的“processed”列中的值为“2”，以便仅返回已完成的销售。我正在努力解决逻辑/语法问题。希望图像是可读的。

Answer 1

试试这个。我已按id加入表purchase_logs，并按processed列过滤结果集，其值等于2

SELECT vcc.name AS item, 
       vcc.prodid, 
       vcc.quantity AS qty,
       UPPER(vcc.custom_message) AS nickname, 
       vfd1.value AS fullname,
       vfd2.value as email, 
       vcc.purchaseid, 
       vcc.price
FROM vxu_4_wpsc_cart_contents AS vcc 
    INNER JOIN vxu_4_wpsc_submited_form_data AS vfd1 ON vcc.purchaseid = vfd1.log_id 
                                                            AND vfd1.form_id = 2 
    INNER JOIN vxu_4_wpsc_submited_form_data AS vfd2 ON vcc.purchaseid = vfd2.log_id 
                                                            AND vfd2.form_id = 9
    INNER JOIN vxu_4_wpsc_purchase_logs AS wpl ON wpl.id = vcc.purchaseid
WHERE wpl.processed = 2