February 23, 2021

MySQL: SELECT an order having 2 or more identical products

SELECT 
    op.order_id, op.product_id, COUNT(op.product_id)
FROM
    order_product op
GROUP BY op.order_id, op.product_id
HAVING COUNT(op.product_id) >= 2
with a join to the actual orders table
SELECT 
    o.order_id, op.product_id, COUNT(op.product_id)
FROM
    `order` o
        INNER JOIN
    order_product op ON (o.order_id = op.order_id)
WHERE
    o.store_id = 18
        AND o.date_added > '2020-01-01 00:00:00'
        AND o.date_added < '2020-12-29 12:08:00'
GROUP BY o.order_id , op.product_id
HAVING COUNT(op.product_id) >= 2
ORDER BY o.date_added DESC

No comments: