CODEWITHSUNDEEP
sqloracle-database
Nendrel
Nendrel

Reputation: 83

Oracle: how to get only rows with a specific condition in columns

I have three tables, customers, orders and customers-orders like this:

Customers:
    ID   NAME
    1    Peter
    2    Jack
    3    Lisa

Orders:
    ID   INIT_DATE    END_DATE
    1    10-11-2014   10-11-2015
    2    23-11-2014   01-01-2015
    3    23-11-2014   03-05-2015
    4    04-04-2016   08-11-2016
    5    13-07-2016   01-11-2016
    6    04-06-2016   30-10-2016
    7    12-11-2014   01-05-2015
    8    26-11-2014   10-10-2015
    9    05-09-2016   11-11-2016

Customers_Orders:
   CUSTOMER_ID   ORDER_ID
   1             1
   1             2
   1             3
   2             4
   2             5
   2             6
   3             7
   3             8
   3             9

I need to get customers who have all orders outdated. I mean, those orders which sysdate is not between init_date and end_date. In this case, the expected result is 1-Peter.

What i tried is this:

SELECT *
FROM Customers
WHERE
ID NOT IN (
  SELECT DISTINCT Customers_Orders.CUSTOMER_ID
  FROM Customers_Orders, Orders
  WHERE Customers_Orders.ORDER_ID = Orders.ID
  AND Orders.INIT_DATE <= SYSDATE AND Orders.END_DATE > SYSDATE
)

But i don't like the "NOT IN" syntax because the low performance. Is there another way?

Note: date format is dd-mm-yyyy

Edit: I corrected the question (in bold).

Upvotes: 0

Views: 69

Answers (3)

JohnHC
JohnHC

Reputation: 11195

This will exclude all customers where the INIT_DATE is less than today and the END_DATE > today.

with Curr_ord as
(
select co1.customer_id, co1.order_id
from  customers_orders co1
inner join orders o1
on o1.id = co1.order_id
where o1.INIT_DATE <= sysdate
and o1.END_DATE > sysdate
)

select C1.*
from Customers C1
left join Curr_Ord CO2
on C1.Customer_ID = CO2.Customer_ID

where CO2.Customer_ID is null

Upvotes: 2

Michael Broughton
Michael Broughton

Reputation: 4055

Why not just do the join?

  SELECT DISTINCT C.CUSTOMER_ID
    FROM customers c
       JOIN Customers_Orders co on co.customer_id = c.customer_id
       JOIN Orders od on od.order_id = co.order_id
    WHERE Od.INIT_DATE <= SYSDATE 
      AND Od.END_DATE > SYSDATE

With the right indexing on orders, you might get the performance gain by putting the filter into the join:

  SELECT DISTINCT C.CUSTOMER_ID
    FROM customers c
       JOIN Customers_Orders co on co.customer_id = c.customer_id
       JOIN (SELECT od.order_id 
               from Orders 
               WHERE Od.INIT_DATE <= SYSDATE 
                 AND Od.END_DATE > SYSDATE )  od on od.order_id = co.order_id

but now we're talking performance tuning strategies.... and in this case we would need to talk about your data profiles etc to get the optimal solution.

Upvotes: -1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Join the three tables and use conditional aggregation to check if all the orders for a customer are outdated.

select c.id,c.name
from customer_orders co
join orders o on o.id=co.order_id
join customers c on c.id=co.customer_id
group by c.id,c.name
having count(case when sysdate between co.init_date and co.end_date then 1 end) = 0

Upvotes: 2

Related Questions