Get all orders that are "ordered" but not "paid" 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.
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:



Nice and efficient!