3

Let's say I have two tables. Order and OrderState.

Order
ID|Description|
1 | tv order
2 | computer order

OrderState
ID|Description|OrderID
1 |ordered    | 1
2 |paid       | 1
3 |received   | 1 
4 |ordered    | 2

How would one get all orders that are "ordered" but not "paid"?

SELECT * FROM Order as o
    INNER JOIN OrderState as os
        on o.ID = os.OrderID
WHERE os.Description='Ordered' AND NOT EXISTS(
    SELECT * FROM Order as o
        INNER JOIN OrderState as os
            on o.ID = os.OrderID
    WHERE os.Description='paid');

I tried the query above but it does not seem to work.

  • 3
    Worth asking: why separate rows for this? An order can logically only have one state. If you need a record of timestamps or something, you could have a timestamp column indicating when that state was completed. – jpmc26 Sep 29 '15 at 18:14
4

For a different method to obtain the orders that are only in a given state, such as "Ordered", but not "Recieved", you could use something like the following:

use tempdb;

CREATE TABLE dbo.OrderStatus
(
    OrderStatusID INT NOT NULL
        CONSTRAINT PK_OrderStatus
        PRIMARY KEY CLUSTERED
    , OrderStatusDesc VARCHAR(255) NOT NULL
);

CREATE TABLE dbo.Orders
(
    OrderID INT NOT NULL
        CONSTRAINT PK_Orders
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , OrderDesc VARCHAR(255) NOT NULL
);

CREATE TABLE dbo.Orders_OrderStatus
(
    OrderID INT NOT NULL
        CONSTRAINT FK_Orders
        FOREIGN KEY REFERENCES dbo.Orders(OrderID)
    , OrderStatusID INT NOT NULL
        CONSTRAINT FK_OrderStatusID
        FOREIGN KEY REFERENCES dbo.OrderStatus(OrderStatusID)
    , CONSTRAINT PK_Orders_OrderStatus
        PRIMARY KEY CLUSTERED
        (OrderID, OrderStatusID)
);

Populate some sample data:

INSERT INTO dbo.OrderStatus (OrderStatusID, OrderStatusDesc)
VALUES (1, 'Ordered')
    , (2, 'Paid')
    , (4, 'Received');

INSERT INTO dbo.Orders (OrderDesc)
VALUES ('TV')
    , ('Computer');

INSERT INTO dbo.Orders_OrderStatus (OrderID, OrderStatusID)
VALUES (1, 1) -- TV, Ordered
    , (1, 2)  -- TV, Paid
    , (1, 4)  -- TV, Received
    , (2, 1); -- Computer, Ordered

If you want to see the description of the order, you can do a simple join and an aggregate to get the list:

SELECT o.OrderDesc
FROM dbo.Orders o
    INNER JOIN dbo.Orders_OrderStatus oos ON o.OrderID = oos.OrderID
GROUP BY o.OrderID, o.OrderDesc
HAVING SUM(oos.OrderStatusID) = 1;

If, however, you only need a list of OrderIDs, you can simplify further to just:

SELECT oos.OrderID
FROM dbo.Orders_OrderStatus oos
GROUP BY oos.OrderID
HAVING SUM(oos.OrderStatusID) = 1;

If you wanted to see a list of orders that were both "Ordered" and "Received", you could change the above query to:

SELECT oos.OrderID
FROM dbo.Orders_OrderStatus oos
GROUP BY oos.OrderID
HAVING SUM(oos.OrderStatusID) = 3;

Indeed, if you need to see a list of orders that have been "Ordered", "Received", and "Paid", you would do:

SELECT oos.OrderID
FROM dbo.Orders_OrderStatus oos
GROUP BY oos.OrderID
HAVING SUM(oos.OrderStatusID) = 7;

The above uses SQL Server syntax, although it would be straight-forward enough to convert this to other RDBMSs that use SQL syntax.

The advantage of using this type structure is scalability. Time required for searching orders with any given status is a linear, or O(n) operation.

Let's try it with a tiny bit more data. In order to run this you'll need to drop the tables above, then recreate them.

Create 100,000 orders:

INSERT INTO dbo.Orders (OrderDesc)
SELECT TOP(100000) o.name + '.' + o1.name + '.' + o2.name
FROM sys.objects o, sys.objects o1, sys.objects o2;

Mark all of them as "Ordered":

INSERT INTO dbo.Orders_OrderStatus (OrderID, OrderStatusID)
SELECT TOP(100000) ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
    , 1
FROM sys.objects o, sys.objects o1, sys.objects o2;

Mark 60,000 of them as "Paid":

INSERT INTO dbo.Orders_OrderStatus (OrderID, OrderStatusID)
SELECT TOP(60000) ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
    , 2
FROM sys.objects o, sys.objects o1, sys.objects o2;

Mark 10,000 of them as "Recieved":

INSERT INTO dbo.Orders_OrderStatus (OrderID, OrderStatusID)
SELECT TOP(10000) ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id)
    , 4
FROM sys.objects o, sys.objects o1, sys.objects o2;

Select all orders that have been "Ordered", and "Paid":

SELECT oos.OrderID
FROM dbo.Orders_OrderStatus oos
GROUP BY oos.OrderID
HAVING SUM(oos.OrderStatusID) = 3;

On my desktop machine, this results in the following stats:

(50000 row(s) affected)
Table 'Orders_OrderStatus'. Scan count 1, logical reads 490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 286 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

With an execution plan:

enter image description here

Nice and efficient!

| improve this answer | |
3

Given your scenario, I have created the test data with the following script

CREATE TABLE Orders 
(
    id int IDENTITY(1,1) PRIMARY KEY clustered
    ,DESCRIPTION varchar(30)
);
GO

INSERT INTO dbo.Orders
        ( DESCRIPTION )
VALUES  ( 'tv order'), ( 'computer order');
GO

CREATE TABLE OrderState
(
    id INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
    ,Description VARCHAR(30)
    ,OrderID INT
);
GO
INSERT INTO dbo.OrderState
        ( Description, OrderID )
VALUES  ( 'ordered', 1  ), ( 'paid', 1  ), ( 'received', 1  ), ( 'ordered', 2  );
GO

Selecting against my data to get the orders where they are ordered but not paid I would run teh following:

SELECT os.orderid
FROM dbo.OrderState AS os
LEFT JOIN dbo.OrderState AS os2 ON os.orderid = os2.OrderID AND os2.Description = 'paid'
WHERE os.Description = 'ordered'
AND os2.id IS NULL;

There are many ways to get this, but this will do.

| improve this answer | |
  • 3
    I think the LEFT join should be Order LEFT JOIN OrderState as they probably want more columns from Order than the ID. – ypercubeᵀᴹ Sep 29 '15 at 16:22
3

Your query is almost correct, but you don't need the Order table in the NOT EXISTS, should be a Correlated Subquery instead:

SELECT * FROM Order as o
    INNER JOIN OrderState as os
        on o.ID = os.OrderID
WHERE os.Description='Ordered' 
AND NOT EXISTS(
    SELECT * FROM OrderState as os
     WHERE o.ID = os.OrderID
       AND os.Description='paid');
| improve this answer | |
3
SELECT 
    * -- You should specify your desired columns instead
FROM dbo.[Order] AS o -- Assuming your schema is 'dbo' and SQL Server
   INNER JOIN dbo.OrderState AS os ON o.ID = os.OrderID
WHERE 
   os.Description = 'ordered' 
   AND 
   o.OrderID NOT IN (
                      SELECT 
                        OrderID 
                      FROM OrderState
                      WHERE 
                        Description = 'paid'
                   );
| improve this answer | |
3

One more answer, just for fun and only for DBMS* that have implemented EXCEPT:

SELECT 
    o.*                   -- specify the desired columns instead of all
FROM 
    schema."Order" AS o   -- replace "schema" with the schema name
WHERE
    o.ID IN
    ( SELECT os.OrderID   
      FROM schema.OrderState AS os 
      WHERE os.Description = 'ordered' 
    EXCEPT 
      SELECT os.OrderID   
      FROM schema.OrderState AS os 
      WHERE os.Description = 'paid'
    ) ;

* (SQL Server, PostgreSQL, DB2, Oracle). If in Oracle, replace EXCEPT with MINUS and remove all the AS:

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.