I want to fetch orders that have a “Received” (ActivityID = 1) activity but not a “Delivered” (ActivityID = 4) activity on orders table. i.e orders that are received but not deliverd yet.
my query is
SELECT OrderID FROM tblOrderActivity
where (tblOrderActivity.ActivityID = 1 AND tblOrderActivity.ActivityID != 4)
GROUP BY OrderID
it is not returning desired result. result should be orderID 2 and 4
Your query doesn't really make sense. Grouping happens after WHERE clause, so you're basically getting all orders that have ActivityID ==1 (because if activity Id is 1 there it's always not equal to 4).
After WHERE clause is applied you end up with following rows:
OrderID ActivityID
1 1
2 1
3 1
4 1
And these are the orders you group. No more condition is evaluated.
If 4 is the highest possible ActivityID you could do following:
SELECT OrderID
FROM tblOrderActivity
GROUP BY OrderID
HAVING MAX(ActivityID) < 4
HAVING condition is applied after grouping, which is what you want.
OrderId and returns only those orders that have MAX(ActivityID) lower than 4.I don't think Group by is needed here. You can use a Subquery to find he order's which is not delivered. Try this.
SELECT *
FROM Yourtable a
WHERE a.ActivityID = 1
AND NOT EXISTS (SELECT 1
FROM yourtable b
WHERE a.OrderID = b.OrderID
AND b.ActivityID = 4)
