how to find missing rows in oracle

Question

Hi, I have a table called 2 tables

create table ORDERS (
    ORDER_NO NUMBER(38,0) not null,
    ORDER_DATE DATE not null,
    SHIP_DATE DATE null,
    SHIPPING_METHOD VARCHAR2(12) null,
    TAX_STATUS CHAR(1) null,
    SUBTOTAL NUMBER null,
    TAX_AMT NUMBER null,
    SHIPPING_CHARGE NUMBER null,
    TOTAL_AMT NUMBER null,
    CUSTOMER_NO NUMBER(38,0) null,
    EMPLOYEE_NO NUMBER(38,0) null,
    BRANCH_NO NUMBER(38,0) null,
    constraint ORDERS_ORDERNO_PK primary key (ORDER_NO) ); 

and

create table PAYMENTS (
    PAYMENT_NO NUMBER(38,0) NOT NULL,       
    CUSTOMER_NO NUMBER(38,0) null,
    ORDER_NO NUMBER(38,0) null,
    AMT_PAID NUMBER NULL,
    PAY_METHOD VARCHAR(10) NULL,
    DATE_PAID DATE  NULL,
    LATE_DAYS NUMBER NULL,
    LATE_FEES NUMBER NULL,
    constraint PAYMENTS_PAYMENTNO_PK primary key (PAYMENT_NO) );

I am trying to find how many late orders each customer have. the column late_days in PAYMENTS table has how many days the customer is late for making payments for any particular order.

so I am making this query

SELECT C.CUSTOMER_NO, C.lname, C.fname, sysdate, COUNT(P.ORDER_NO) as number_LATE_ORDERS
FROM CUSTOMER C, orders o, PAYMENTS P
WHERE C.CUSTOMER_NO = o.CUSTOMER_NO
AND P.order_no = o.order_no
AND P.LATE_DAYS>0
group by C.CUSTOMER_NO, C.lname, C.fname

That means, I am counting the orders those have any late payments and late_days>0. But this is giving me only the customers who have any orders with late_days>0, but the customers who does not have any late orders are not showing up. so if one customer has 5 orders with late payments then it is showing 5 for that customer, but if a customer have 0 late orders,that customer is not selected in this query. Is there any way to select all the customers , and if he has any late orders, it will show the number and also if he does not have any late orders, it will show 0.

Answer 1

Do you have customers without any orders and, if so, do you want to show them ? I'll assume either no or "yes and no".

SELECT C.CUSTOMER_NO, C.lname, C.fname, sysdate, COUNT(P.ORDER_NO) as number_LATE_ORDERS
FROM CUSTOMER C 
  join orders o on C.CUSTOMER_NO = o.CUSTOMER_NO
  left outer join PAYMENTS P on P.order_no = o.order_no and P.LATE_DAYS>0
group by C.CUSTOMER_NO, C.lname, C.fname

Answer 2

I would pull all customer/order/payments and then just determine the number of late orders in your select based on the late_days field. This presumes only 1 late payment row per order row.

select c.customer_no, 
       c.lname, 
       c.fname, 
       sysdate,
       sum(case when p.late_days > 0 then 1
                else 0
       end) number_late_orders
  from customer c, 
       orders o, 
       payments p
 where c.customer_no = o.customer_no
   and p.order_no = o.order_no
group by c.customer_no, 
         c.lname, 
         c.fname

Answer 3

One Inner and one outer join should do the trick. I have not tested it, but I think that a NULL value is not counted, thus all customers that have orders will show, but only payments that have late_days> 0 will have entries and can be counted. Those customers that do not have a result in the sub-select will produce a NULL entry in p.order_no which the COUNT() function should not count.

SELECT C.CUSTOMER_NO, C.lname, C.fname, sysdate, COUNT(P.ORDER_NO) as number_LATE_ORDERS
FROM CUSTOMER C 
INNER JOIN orders o ON (C.CUSTOMER_NO = o.CUSTOMER_NO) 
LEFT OUTER JOIN (SELECT * FROM PAYMENTS WHERE p.late_days > 0) P ON (P.order_no = o.order_no)
GROUP by C.CUSTOMER_NO, C.lname, C.fname