How to avoid showing data on each row [message #636661] |
Thu, 30 April 2015 01:47 |
|
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 #636665 is a reply to message #636661] |
Thu, 30 April 2015 01:56 |
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 #636676 is a reply to message #636671] |
Thu, 30 April 2015 02:52 |
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 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
kaz123 wrote on Thu, 30 April 2015 13:06Thanks 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 #636922 is a reply to message #636661] |
Tue, 05 May 2015 11:32 |
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;
|
|
|