CODEWITHSUNDEEP
mysqlsql
sbrbot
sbrbot

Reputation: 6447

Filtering orders having one item and not having another one in the same time

I have very simple one-to-many relation (tables Orders-Items) where one order can have zero or more items.

What's the best way (SQL query) to filter only orders having item1 (item_id=1) and no item2 (item_id=2) in the same time?

At the moment I use the following SQL query:

SELECT o.order_id
  FROM orders o
  JOIN items i ON i.order_id=o.order_id
 WHERE i.item_id=1
   AND o.order_id NOT IN (SELECT o2.order_id
                            FROM orders o2
                            JOIN items i2 ON i2.order_id=o2.order_id
                           WHERE i2.item_id=2)

Upvotes: 1

Views: 270

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

I prefer to approach these types of questions using group by and having:

SELECT i.order_id
FROM items i
GROUP BY i.order_id
HAVING SUM(i.item_id = 1) > 0 AND
       SUM(i.item_id = 2) = 0;

Some notes:

  • You don't need to join in orders, because you have order_id in items.
  • Each condition in the having clause is counting the number of items. The first says there is at least one of item 1 and the second that there is no item 2.
  • I removed the where clause. If you were to have one, then it would be WHERE i.item_id IN (1, 2).

EDIT:

In any database other than MySQL, you would use this HAVING clause:

HAVING SUM(CASE WHEN i.item_id = 1 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN i.item_id = 2 THEN 1 ELSE 0 END) = 0;

This will work in MySQL as well; I just like the shorter notation.

Upvotes: 2

fancyPants
fancyPants

Reputation: 51888

No need for a correlated subquery, use the HAVING clause.

This excludes all order_ids where an item_id = 2 exists.

SELECT o.order_id
FROM orders o
JOIN items i ON i.order_id=o.order_id
WHERE i.item_id=1
GROUP BY o.order_id
HAVING SUM(item_id = 2) = 0;

Or you could do

SELECT o.order_id
FROM orders o
JOIN items i ON i.order_id=o.order_id
WHERE i.item_id=1
GROUP BY o.order_id
HAVING COUNT(DISTINCT item_id) = 1;

to make sure it has only item_id = 1. Several ways to do it, depending on what you want to do exactly.

Upvotes: 1

Related Questions