mysql - 仅过滤 JOIN 中我通过复选框选择的项目的订单

Question

MYSQL

我想根据表单复选框中的项目来过滤“订单”。

例如，我检查了项目 A + 项目 B，但不是项目 C

订单 1

项目A

订单 2

项目B

订单 3

项目A

项目B

项目C

订单 4

项目A

项目B

我想要的是:

订单 1 & 订单 2 & 订单 4，但不是订单 3，因为这也有项目 C

我的查询是这样的

SELECT
P.id as product_id,P.name as product_name,
O.id as order_id,O.group_id,O.payment_date,O.payment_type_id,I.quantity,
M.name,M.surname,M.email
FROM tbl_orders O
LEFT JOIN tbl_order_items I on I.order_id = O.id
LEFT JOIN tbl_products P on P.id = I.product_id
LEFT JOIN tbl_members M on M.id = O.member_id
WHERE
     I.product_id in (1044,1129,20976,16775)
AND
     O.status_id = 311 and O.payment_status_id = 349

谢谢

Answer 1

我猜测核心问题是您想要排除具有任何未检查值的订单，并包含至少具有一个检查值的订单。

SELECT
P.id as product_id,P.name as product_name,
O.id as order_id,O.group_id,O.payment_date,O.payment_type_id,I.quantity,
M.name,M.surname,M.email
FROM tbl_orders O -- start with orders

-- add in the items, but use INNER to avoid orders that do not have
-- at least one order_item whose id was checked
INNER JOIN tbl_order_items I on I.order_id = O.id
           AND I.product_id in (1044,1129,20976,16775)
-- left join in the items which were NOT checked.  If there are any, the
-- rows will have bad product ids in them.
-- But if there ARE NOT any bad items in the order, 
-- then there will only be one row with NULL for BADITEMS.product_id
LEFT JOIN tbl_order_items BADITEMS on BADITEMS.order_id = O.id
           AND BADITEMS.product_id NOT in (1044,1129,20976,16775)
-- Pull in the product information
INNER JOIN tbl_products P on P.id = I.product_id
-- Pull in member information
LEFT JOIN tbl_members M on M.id = O.member_id
WHERE
     -- the inner join has already insured that we only get orders
     -- that have checked items.
     -- To remove the orders that ALSO had unchecked items,
     -- We take only rows where the BADITEMS join failed.
     BADITEMS.product_id IS NULL
AND
     O.status_id = 311 and O.payment_status_id = 349