complicated Select Statement Order-Order Details

What is the query to retrive? All orders that, do not display orders where all the order_Details contain a code = ERD.

Order Table
OrderID OrderName
01            OfficeOrderA
02            OfficeOrderB
03            OfficeOrderC

Order_Detail Table
OrDetailID OrderID ItemName Code1            
01               01         pencil      ERD
02               01         ballpen       ERD
03               01         stappler      CDG
04               02         chair      ERD
05               02         table      ERD


Do dot display orders where all the order_Details
contain a code = ERD.

Select * from Orders where (?)

Thanks,
BG
LVL 1
blueglacierAsked:
Who is Participating?
 
Raynard7Connect With a Mentor Commented:
sorry - so you want ALL the order details as erd

select o.* from Orders o where o.OrderID not in
(
select
    x.orderID
from
(
select od1.orderID, count(*) cx, sum(case when od1.code1 = 'ERD' then 1 else 0 end)  sx from  Order_Detail od1 group by od1.orderID
) x
where x.cx = x.sx
)

may work
0
 
Raynard7Commented:
select o.* from Orders o where o.OrderID not in (select orderID from Order_Detail od where od.Code1 = 'ERD')
0
 
blueglacierAuthor Commented:
Ray

I want to Discard those orders that have all their Order Detail rows as ERD.

On the example tables above, only Order 1 will be returned. Becuase Order 2 has all the Order Details with code = ERD.

your solution will elimitate order 1 and 2.

BG
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sujith80Connect With a Mentor Commented:
select a.*
from orders a, (select distinct orderId from order_details where code1 <> 'ERD') b
where a.orderId = b.orderId
0
 
kimarti3Connect With a Mentor Commented:
select * from orders where order_id in (select order_id from order_details where code1 != 'ERD')
0
 
RainMan82Connect With a Mentor Commented:
select *
  from order a,
       order_detail b
 where a.orderid = b.orderid
   and a.orderid not in (select distinct x.orderid from order_detail x where x.code1 = 'ERD')
0
 
desiboy1974Connect With a Mentor Commented:
select *
  from order a
  where not exists(select 1 from order_detail b
where b.order_id=a.order_id
and b.code='ERD')
0
All Courses

From novice to tech pro — start learning today.