Forum Stats

  • 3,734,266 Users
  • 2,246,933 Discussions
  • 7,857,216 Comments

Discussions

How to Avoid Duplicate Appearance of Data

Kaz123
Kaz123 Member Posts: 9
edited Apr 29, 2015 4:11PM in SQL

Hi All,

I have created three tables: customer, customer_payment, customer_receipt. The payment table has two entries and the receipt table has one.

But when I query the tables together, the receipt amount is repeated against both entries of payments. How can I avoid this?

I am getting below result:

CUSTOMER_IDCUSTOMER_NAMEPAYMENT_AMOUNTRECEIPT_AMOUNT
1ABC CO.100100
1ABC CO.200100
2XYZ CO.

I want it like this:

CUSTOMER_IDCUSTOMER_NAMEPAYMENT_AMOUNTRECEIPT_AMOUNT
1ABC CO.100100
1ABC CO.200

I have given below the DDL/DML for further clarity and reuse.

CREATE TABLE CUSTOMER(CUSTOMER_ID NUMBER, CUSTOMER_NAME VARCHAR2(30));

CREATE TABLE CUSTOMER_PAYMENTS (PAYMENT_ID NUMBER, CUSTOMER_ID NUMBER, PAYMENT_AMOUNT NUMBER);

CREATE TABLE CUSTOMER_RECEIPTS (RECEIPT_ID NUMBER, CUSTOMER_ID NUMBER, RECEIPT_AMOUNT NUMBER);

INSERT INTO CUSTOMER VALUES(1, 'ABC CO.');
INSERT INTO CUSTOMER VALUES(2, 'XYZ CO.');

INSERT INTO CUSTOMER_PAYMENTS VALUES (1, 1, 100);

INSERT INTO CUSTOMER_RECEIPTS VALUES (1, 1, 100);
INSERT INTO CUSTOMER_PAYMENTS VALUES (2, 1, 200);


SELECT * FROM CUSTOMER;
SELECT * FROM CUSTOMER_PAYMENTS;
SELECT * FROM CUSTOMER_RECEIPTS;

SELECT
   C.CUSTOMER_ID,
   C.CUSTOMER_NAME,
   CP.PAYMENT_AMOUNT,
   CR.RECEIPT_AMOUNT
FROM
   CUSTOMER C
   LEFT OUTER JOIN CUSTOMER_PAYMENTS CP
   ON CP.CUSTOMER_ID = C.CUSTOMER_ID
   LEFT OUTER JOIN CUSTOMER_RECEIPTS CR
   ON CR.CUSTOMER_ID = C.CUSTOMER_ID

Thanks,

Kaz

Sign In or Register to comment.