Home » SQL & PL/SQL » SQL & PL/SQL » How to avoid showing data on each row (Oracle 11g XE)
How to avoid showing data on each row [message #636661] Thu, 30 April 2015 01:47 Go to next message
kaz123
Messages: 5
Registered: April 2015
Junior Member
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_ID	CUSTOMER_NAME	PAYMENT_AMOUNT	RECEIPT_AMOUNT
1	        ABC CO.	             100	100
1	        ABC CO.	               200	100
2	        XYZ CO.




I want it like this:

CUSTOMER_ID	CUSTOMER_NAME	PAYMENT_AMOUNT	RECEIPT_AMOUNT
1	        ABC CO.	             100	100
1	        ABC 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


Lalit : Added code tags

[Updated on: Thu, 30 April 2015 01:51] by Moderator

Report message to a moderator

Re: How to avoid showing data on each row [message #636663 is a reply to message #636661] Thu, 30 April 2015 01:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Hi,

Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.
Re: How to avoid showing data on each row [message #636665 is a reply to message #636661] Thu, 30 April 2015 01:56 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Thanks for providing the test case.

You need to avoid the LEFT OUTER JOINs.

SQL> SELECT
  2     C.CUSTOMER_ID,
  3     C.CUSTOMER_NAME,
  4     CP.PAYMENT_AMOUNT,
  5     CR.RECEIPT_AMOUNT
  6  FROM
  7     CUSTOMER C
  8     JOIN CUSTOMER_PAYMENTS CP
  9     ON CP.CUSTOMER_ID = C.CUSTOMER_ID
 10     JOIN CUSTOMER_RECEIPTS CR
 11     ON CR.CUSTOMER_ID = C.CUSTOMER_ID
 12     ORDER BY PAYMENT_AMOUNT;

CUSTOMER_ID CUSTOMER_NAME                  PAYMENT_AMOUNT RECEIPT_AMOUNT
----------- ------------------------------ -------------- --------------
          1 ABC CO.                                   100            100
          1 ABC CO.                                   200            100

SQL>



Regards,
Lalit

[Updated on: Thu, 30 April 2015 01:59]

Report message to a moderator

Re: How to avoid showing data on each row [message #636671 is a reply to message #636665] Thu, 30 April 2015 02:36 Go to previous messageGo to next message
kaz123
Messages: 5
Registered: April 2015
Junior Member
Thanks Lalit.
I have just one row in Customer_Receipts table but the receipt amount is appearing on both rows. Is it possible to show receipt amount only once?
Re: How to avoid showing data on each row [message #636675 is a reply to message #636671] Thu, 30 April 2015 02:49 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Which row should it show on?
Re: How to avoid showing data on each row [message #636676 is a reply to message #636671] Thu, 30 April 2015 02:52 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
To do it correctly, you should probably be passing an id of the receipt table in. Or some other manner of key.

The DB is returning ALL rows which match, if you want to limit these logically, you need to add a WHERE clause with that logic within.
Re: How to avoid showing data on each row [message #636686 is a reply to message #636671] Thu, 30 April 2015 04:27 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
kaz123 wrote on Thu, 30 April 2015 13:06
Thanks Lalit.
I have just one row in Customer_Receipts table but the receipt amount is appearing on both rows. Is it possible to show receipt amount only once?


Then you need to alter the design of your CUSTOMER_RECEIPTS table. I think the design is inappropriate and you should use the PAYMENT_ID as the foreign key rather than CUSTOMER_ID.

Do it this way:

Setup

DROP TABLE CUSTOMER PURGE;
DROP TABLE CUSTOMER_PAYMENTS PURGE;
DROP TABLE CUSTOMER_RECEIPTS PURGE;

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);
CREATE TABLE CUSTOMER_RECEIPTS (RECEIPT_ID NUMBER, PAYMENT_ID NUMBER, RECEIPT_AMOUNT NUMBER); --> Notice the change

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_PAYMENTS VALUES (2, 1, 200);

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

COMMIT;


Query

SQL> SELECT
  2     C.CUSTOMER_ID,
  3     C.CUSTOMER_NAME,
  4     CP.PAYMENT_AMOUNT,
  5     CR.RECEIPT_AMOUNT
  6  FROM
  7     CUSTOMER C
  8     JOIN CUSTOMER_PAYMENTS CP
  9     ON CP.CUSTOMER_ID = C.CUSTOMER_ID
 10     LEFT OUTER JOIN CUSTOMER_RECEIPTS CR
 11     ON CR.PAYMENT_ID = CP.PAYMENT_ID;

CUSTOMER_ID CUSTOMER_NAME                  PAYMENT_AMOUNT RECEIPT_AMOUNT
----------- ------------------------------ -------------- --------------
          1 ABC CO.                                   100            100
          1 ABC CO.                                   200

SQL>



Regards,
Lalit

[Updated on: Thu, 30 April 2015 04:30]

Report message to a moderator

Re: How to avoid showing data on each row [message #636700 is a reply to message #636686] Thu, 30 April 2015 06:17 Go to previous messageGo to next message
kaz123
Messages: 5
Registered: April 2015
Junior Member
I cannot change the tables structure. Payments and receipts are two separate independent tables. Both of these tables has customers table as the parent table. The query output you have shown is what I want but I want to achieve this without making any change in the table structure.
Re: How to avoid showing data on each row [message #636704 is a reply to message #636700] Thu, 30 April 2015 07:31 Go to previous messageGo to next message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Imagine that you add another record to the receipt table for the same customer, say
INSERT INTO CUSTOMER_RECEIPTS VALUES (2, 1, 50);

What should your output become ?
Re: How to avoid showing data on each row [message #636708 is a reply to message #636704] Thu, 30 April 2015 09:53 Go to previous messageGo to next message
kaz123
Messages: 5
Registered: April 2015
Junior Member
The new output should be:

CUSTOMER_ID CUSTOMER_NAME PAYMENT_AMOUNT RECEIPT_AMOUNT
----------- ------------------------------ -------------- --------------
1 ABC CO. 100 100
1 ABC CO. 200 50


And if we add another receipt:
INSERT INTO CUSTOMER_RECEIPTS VALUES (3, 1, 300);

Then the output would be:

CUSTOMER_ID CUSTOMER_NAME PAYMENT_AMOUNT RECEIPT_AMOUNT
----------- ------------------------------ -------------- --------------
1 ABC CO. 100 100
1 ABC CO. 200 50
1 ABC CO. 300

Re: How to avoid showing data on each row [message #636710 is a reply to message #636708] Thu, 30 April 2015 09:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Lalit Kumar B wrote on Thu, 30 April 2015 08:50
Hi,

Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.

Re: How to avoid showing data on each row [message #636711 is a reply to message #636710] Thu, 30 April 2015 10:11 Go to previous messageGo to next message
kaz123
Messages: 5
Registered: April 2015
Junior Member
Sorry, I am reposting the last post in a more readable format.

The new output should be:
 
CUSTOMER_ID  CUSTOMER_NAME                  PAYMENT_AMOUNT RECEIPT_AMOUNT
 ----------- ------------------------------ -------------- --------------
 1           ABC CO.                               100          100
 1           ABC CO.                               200           50


And if we add another receipt:
INSERT INTO CUSTOMER_RECEIPTS VALUES (3, 1, 300);

Then the output would be:

 
CUSTOMER_ID  CUSTOMER_NAME                  PAYMENT_AMOUNT RECEIPT_AMOUNT
 ----------- ------------------------------ -------------- --------------
 1           ABC CO.                               100          100
 1           ABC CO.                               200           50
 1           ABC CO.                                            300


Re: How to avoid showing data on each row [message #636922 is a reply to message #636661] Tue, 05 May 2015 11:32 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
It seems to me like the task for FULL OUTER JOIN.

If receipts and payments with the same RECEIPT_ID and PAYMENT_ID should be in one row, it is as simple as
SELECT C.CUSTOMER_ID, C.CUSTOMER_NAME, CP.PAYMENT_AMOUNT, CR.RECEIPT_AMOUNT
FROM CUSTOMER_PAYMENTS CP
  FULL OUTER JOIN CUSTOMER_RECEIPTS CR
   ON CR.CUSTOMER_ID = CP.CUSTOMER_ID and CR.RECEIPT_ID = CP.PAYMENT_ID
  INNER JOIN CUSTOMER C ON nvl(CP.CUSTOMER_ID, CR.CUSTOMER_ID) = C.CUSTOMER_ID;

If they should be only sorted by these IDs, use analytic ROW_NUMBER in subquery to match corresponding ones, something like
SELECT C.CUSTOMER_ID, C.CUSTOMER_NAME, CP.PAYMENT_AMOUNT, CR.RECEIPT_AMOUNT
FROM
   (select cp.*, row_number() over (partition by customer_id order by payment_id) rn
    from CUSTOMER_PAYMENTS cp) CP
   FULL OUTER JOIN
   (select cr.*, row_number() over (partition by customer_id order by receipt_id) rn
   from CUSTOMER_RECEIPTS cr) CR
   ON CR.CUSTOMER_ID = CP.CUSTOMER_ID and CR.rn = CP.rn
  INNER JOIN CUSTOMER C ON nvl(CP.CUSTOMER_ID, CR.CUSTOMER_ID) = C.CUSTOMER_ID;
Previous Topic: need help in query
Next Topic: How to convert CLOB(results of an SQL query) to XMLDocument
Goto Forum:
  


Current Time: Thu Mar 28 10:41:53 CDT 2024