CODEWITHSUNDEEP
mysqlsqldatabasejoin
ksbawpn
ksbawpn

Reputation: 322

SQL Get all orders which contain exclusively one kind of item

In this Database I want to count the number of orders that contain only products of a certain category.

I know how to count all orders that also contain items of a certain category, i.e. category 1:

SELECT Count(DISTINCT orderdetails.orderid) AS "AllCat1" 
FROM   orderdetails 
       INNER JOIN orders 
               ON orderdetails.orderid = orders.orderid 
                  AND orderdetails.productid IN (SELECT DISTINCT productid 
                                                 FROM   products 
                                                 WHERE  categoryid = 1) 
WHERE  orderdate BETWEEN "1996-12-01" AND "1996-12-31";

I am having trouble finding an elegant way to get all orders that contain only category 1 items. I tried selecting all OrderIDs and grouping them by OrderID AND CategoryID: 

SELECT * 
FROM   orderdetails 
       INNER JOIN orders 
               ON orderdetails.orderid = orders.orderid 
                  AND orderdate BETWEEN "1996-12-01" AND "1996-12-31" 
       INNER JOIN products 
               ON orderdetails.productid = products.productid 
GROUP  BY orderdetails.orderid, 
          categoryid;

But I have no idea how to count all OrderIDs that contain category 1 items exclusively. Is my approach right? Or is there a better way to do it (Which I am sure there is)

Upvotes: 1

Views: 2893

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You can use group by and having . . . but you need two levels. To get the orders that are all in one (or a set of categories) by doing:

SELECT o.orderId 
FROM orders o JOIN
     orderdetails od
     ON od.orderid = o.orderid JOIN
     products p
     ON p.productid = od.productid
WHERE o.orderdate BETWEEN '1996-12-01' AND '1996-12-31'
GROUP BY o.orderId
HAVING SUM(CASE WHEN p.categoryid IN (1) THEN 1 ELSE 0 END) = COUNT(*);

The count needs a subquery:

SELECT COUNT(*)
FROM (SELECT o.orderId 
      FROM orders o JOIN
           orderdetails od
           ON od.orderid = o.orderid JOIN
           products p
           ON p.productid = od.productid
      WHERE o.orderdate BETWEEN '1996-12-01' AND '1996-12-31'
      GROUP BY o.orderId
      HAVING SUM(CASE WHEN p.categoryid IN (1) THEN 1 ELSE 0 END) = COUNT(*)
     ) o;

Upvotes: 3

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You can do filtering using HAVING clause. We basically Count the order details rows where category is 1 for an order. It should be equal to the total count of rows for that order. This would ensure that all the categories in an order is 1 only.

SELECT od.orderid 
FROM   orderdetails AS od 
       INNER JOIN orders AS o
               ON od.orderid = o.orderid 
                  AND o.orderdate BETWEEN "1996-12-01" AND "1996-12-31" 
       INNER JOIN products AS p
               ON od.productid = p.productid 
GROUP  BY od.orderid 
HAVING COUNT(CASE WHEN p.categoryid = 1 THEN 1 END) = COUNT(*)

It is advisable to use Aliasing in case of multi-table queries for Code clarity and readability

Upvotes: 2

Related Questions