I’ve been trying to figure this one out for days but can’t come up with a solution. Here are the table schemes.
This is my current query.
x
SELECT DISTINCT `address`, `order`.`id`
FROM `order`, `ordered_articles`
WHERE `order`.`id` = `f_order_id`
AND `Status` > 1
AND `Status` <4;
The problem is that the query returns as long as there is one article with status bigger than 1. I need a query where all the articles of that order have a status bigger than 1.
Advertisement
Answer
You can do it with NOT EXISTS
:
SELECT o.`address`, o.`id`
FROM `order` o
WHERE NOT EXISTS (
SELECT 1 FROM `ordered_articles`
WHERE `f_order_id` = o.`id`
AND (`Status` <= 1 OR `Status` >= 4 )
);
or:
SELECT o.`address`, o.`id`
FROM `order` o INNER JOIN `ordered_articles` i
ON i.`f_order_id` = o.`id`
GROUP BY o.`address`, o.`id`
HAVING SUM(`Status` <= 1 OR `Status` >= 4) = 0;