CODEWITHSUNDEEP
sql
Strom
Strom

Reputation: 129

SQL GROUP BY with subquery

OrderHistory Id , IdOrder , State

and a table Order OrderId , ConfirmationDate , LastState

OrderHistory Example 

1, 1000, 50
2, 1000, 60
3, 1000, 90
4, 1001, 50
5, 1001, 90

Order

1000, '2018-03-01', 90
1001, '2018-03-01', 90

I would take all order which doesn't never passed in state 60. I tried with many query but i still get order by state 60.

SELECT oh.idOrdine  
FROM OrderHistory  oh 
where oh.state not in (60) 
and oh.OrderId in (Select OrderID 
                   From Orders 
                   where ConfirmationDate >= '2017-03-01' 
                     and state= 90) 
group by oh.OrderId 
order by oh.OrderId desc


SELECT * 
FROM Orders o 
  join OrderHistory oh on oh.OrderId = o.ORderId 
where o.ConfirmationDate>= '2017-03-01' 
  and o.state= 90 
  and oh.OrderId in (SELECT OrderId 
                     from OrderHistory 
                     WHERE State not in (60) 
                       and State in (95) 
                     group by ORderID)

What is wrong?

Upvotes: 0

Views: 58

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

If you only want orders that have had some state, then you can also use group by and having:

select oh.orderid
from orderhistory oh
group by oh.orderid
having sum(case when state = 60 then 1 else 0 end) = 0;

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Use NOT EXISTS instead :

select o.*
from Orders o
where not exists (select 1 from OrderHistory oh where oh.OrderId = o.OrderId and oh.State = 60);

Upvotes: 1

Caldazar
Caldazar

Reputation: 3812

Instead of not in , you should use Not Exists. By using Not in, you just eliminate that one row, and not all rows for that OrderID.

SELECT * 
FROM Orders o 
  join OrderHistory oh on oh.OrderId = o.ORderId 
  where o.ConfirmationDate>= '2017-03-01' 
  and o.state= 90 
  and oh.OrderId in (SELECT oh1.OrderId 
                     from OrderHistory oh1
                     WHERE Not Exists (select *
                                       From OrderHistory oh2
                                       where oh2.OrderId=oh1.OrderId and 
                                       oh2.State=60)
                      )

Upvotes: 1

Related Questions