CODEWITHSUNDEEP
sql
Jeff Chambers
Jeff Chambers

Reputation: 33

SQL statement to get values that have one property but not another property

I need a SQL statement that will find all OrderIDs within a table that have an ActivityID = 1 but not an ActivityID = 2

So here's an example table:

OrderActivityTable:

OrderID  // ActivityID
1       // 1
1       // 2
2       // 1
3       // 1

So in this example, OrderID - 1 has activity of 1 AND 2, so that shouldn't return as a result. Orders 2 and 3 have an activity 1, but not 2... so they SHOULD return as a result.

The final result should be a table with an OrderID column with just 2 and 3 as the rows.

What I HAD tried before was:

select OrderID, ActivityID from OrderActivityTable where ActivityID = 1 AND NOT ActivityID = 2

That doesn't seem to get the result I want. I think the answer is a little more complicated.

Upvotes: 3

Views: 4593

Answers (4)

George
George

Reputation: 37

Try this statement:

SELECT OrderID, ActivityID 
FROM OrderActivityTable 
WHERE ActivityID = 1 
AND ActivityID NOT IN (2)

Upvotes: -1

Mike Christensen
Mike Christensen

Reputation: 91628

Something like:

SELECT OrderID, ActivityID FROM OrderActivityTable
WHERE ActivityID = 1
AND OrderID NOT IN (select distinct OrderID from OrderActivityTable where ActivityID = 2);

This will select everything in OrderActivityTable where ActivityID is 1, but filter out the list of OrderID rows that contain an ActivityID of 2.

Upvotes: 2

Barmar
Barmar

Reputation: 781088

You can use an outer self-join for this:

SELECT *
FROM OrderActivityTable t1
LEFT JOIN OrderActivityTable t2
ON t1.OrderID = t2.OrderID AND t2.ActivityID = 2
WHERE t1.ActivityID = 1
AND t2.OrderID IS NULL

You can also use the WHERE NOT IN solution of Mike Christensen, or the correlated subquery solution of alfasin, but in my experience this method usually has the best performance (I use MySQL -- results may be different in other implementations). In an ideal world, the DBMS would recognize that these are all the same, and optimize them equivalently.

Upvotes: 4

Nir Alfasi
Nir Alfasi

Reputation: 53535

select OrderID, ActivityID from OrderActivityTable t
where 
ActivityID = 1 AND  
NOT not exists (select 1 from OrderActivityTable tt 
 where tt.OrderID = t.OrderID AND
 tt.ActivityID = 2)

Upvotes: 0

Related Questions