problem to show result in join tables [message #678087] |
Sat, 09 November 2019 10:23 |
|
hassan08
Messages: 122 Registered: June 2011 Location: egypt
|
Senior Member |
|
|
my tables is
CREATE TABLE TB1(TR_NO NUMBER, TR_DATE DATE);
CREATE TABLE TB2(ID NUMBER,TR_NO NUMBER,TOTAL NUMBER);
tb1 master table
tb2 details table
Insert into HR.TB2
(ID, TR_NO, TOTAL, NOTE)
Values
(1, 1, 50, 'NOTE1');
Insert into HR.TB2
(ID, TR_NO, TOTAL, NOTE)
Values
(2, 1, 50, 'NOTE2');
Insert into HR.TB2
(ID, TR_NO, TOTAL, NOTE)
Values
(6, 2, 60, 'NOTES4');
Insert into HR.TB2
(ID, TR_NO, TOTAL, NOTE)
Values
(5, 1, 60, 'NOTE3');
Insert into HR.TB2
(ID, TR_NO, TOTAL, NOTE)
Values
(4, 2, 60, 'NOTE5');
i want show result like this
tr_no total note
1 0 note1
1 0 note2
1 50 note3
2 0 note4
2 60 note5
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: problem to show result in join tables [message #678098 is a reply to message #678096] |
Sun, 10 November 2019 12:07 |
|
Michel Cadot
Messages: 68658 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:only showing the data of column total only one time
You mean once per transaction.
SQL> break on tr_no dup skip 1
SQL> select * from tb2 order by tr_no, id;
ID TR_NO TOTAL NOTE
---------- ---------- ---------- ----------
1 1 50 NOTE1
2 1 50 NOTE2
5 1 60 NOTE3
4 2 60 NOTE5
6 2 60 NOTES4
Why, in your result, TOTAL with TR_NO=1 is 50 and not 60?
Do you mean in your detail table TOTAL is repeated in each row and this total is the total of the whole transaction?
In this case, your data are wrong and you can see why you must not do that.
[Updated on: Sun, 10 November 2019 12:08] Report message to a moderator
|
|
|
|
|
|