how to find missing rows in oracle
- by user203212
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_days0. But this is giving me only the customers who have any orders with late_days0, 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.
