- 3,734,266 Users
- 2,246,933 Discussions
- 7,857,216 Comments
- 5K All Categories
- 6 Introduce Yourself!
- 290 Community Feedback - NEW! (No Product Questions)
- 66 General Community Platform Concerns/Kudos/Feedback
- 55 Community Platform Bug Reports
- 65 How Do I Use the Community?
- 12 Where is the...? (Community Platform Locations)
- 10 Ideas and Suggestions for the Community Platform
- 65 Personal Document & Blog Archive
- 2 Community Programs
- 2 Get-Togethers
- 4.7K Certification Community
- 4.6K Certification Community Discussions
- 20 Oracle Certified Master Profiles
- 26 Oracle Database 12c Administrator Certified Master Profiles
- 33 Visual Builder Cloud Service
How to Avoid Duplicate Appearance of Data
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:
I want it like this:
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;
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