CODEWITHSUNDEEP
sqloracle11g
Kinan H.
Kinan H.

Reputation: 15

SQL one to many with conditions

I'm working with two tables: table one : order_table table two: order_details

order_table:
 - Order ID (pk)
 - Submitter
 - Date

order_detail:
 - Order_detail_id (unique, auto generated)
 - parent_order (fk, points to the order_id this line is part of)
 - date
 - assigned_to (i.e. department1, department2, department3, department4)

etc.

One order can have multiple order details For instance

Order_detail_id     parent_order    assigned_to
0000001 0010    department1
0000002 0010    department2
0000003 0010    department1
0000004 0010    department1
0000005 0011    department1
0000006 0011    department2
0000007 0011    department3

As you can see, an order can be assigned back and forth between departments.

What I’m trying to do is to get a list of all the orders that were never assigned to a specific department regardless of how many times they were assigned back and forth. For instance, all the unique order numbers that were never assigned at any point to department2 So far I’m getting either nothing or all data that includes assignment de department2…

I've tried so many different ways and I'm unable to get the expected results. thanks for your help

Upvotes: 0

Views: 25

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

Here is one way to get orders that were never assigned to a specific department:

select od.parent_order
from order_detail od
group by od.parent_order
having sum(case when assigned_to = 'department2' then 1 else 0 end) = 0;

I like this method because it is quite general. In your case, the following is probably more efficient:

select o.*
from orders o
where not exists (select 1
                  from order_details od
                  where od.parent_order = o.orderid and
                        od.assigned_to = 'department2'
                 );

Upvotes: 2

Related Questions