Home » SQL & PL/SQL » SQL & PL/SQL » problem to show result in join tables
problem to show result in join tables [message #678087] Sat, 09 November 2019 10:23 Go to next message
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 #678088 is a reply to message #678087] Sat, 09 November 2019 10:41 Go to previous messageGo to next message
John Watson
Messages: 8086
Registered: January 2010
Location: Global Village
Senior Member
What SQL have you tried so far?
Re: problem to show result in join tables [message #678089 is a reply to message #678087] Sat, 09 November 2019 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why didn't you feedback and thank people for the solutions they gave you in your previous topics?

Re: problem to show result in join tables [message #678090 is a reply to message #678087] Sat, 09 November 2019 10:53 Go to previous messageGo to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member
my sql is but not solved my problem
SELECT tb1.tr_no,
         tr_date,
         note,
         CASE
            WHEN COUNT (tb1.tr_no) > 1
            THEN
               COUNT (total)
                  KEEP (DENSE_RANK LAST ORDER BY tb1.tr_no)
                  OVER (PARTITION BY tb1.tr_no)
            ELSE
               total
         END
            total
    FROM tb1, tb2
   WHERE tb1.tr_no = tb2.tr_no
GROUP BY tb1.tr_no,
         tr_date,
         total,
         note
Re: problem to show result in join tables [message #678091 is a reply to message #678089] Sat, 09 November 2019 11:39 Go to previous messageGo to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member
Michel Cadot wrote on Sat, 09 November 2019 10:45

Why didn't you feedback and thank people for the solutions they gave you in your previous topics?

really i forget thank for your help and please help me for this topic
Re: problem to show result in join tables [message #678092 is a reply to message #678091] Sat, 09 November 2019 12:00 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to explain your problem.

Re: problem to show result in join tables [message #678093 is a reply to message #678092] Sat, 09 November 2019 12:22 Go to previous messageGo to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member
i have two tables master and details
my problem is
when join between two tables and executing the query the total repeating every time with the column tr_no
but i want only showing the data after executing the query like this only showing total once
for every detail because if the total repeated the total will be wrong
Re: problem to show result in join tables [message #678094 is a reply to message #678093] Sat, 09 November 2019 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the clear specification of the problem?
Not what is your problem to solve it.

What is the specification of each line and for each line, of each column?
What "total" is total of?

Re: problem to show result in join tables [message #678095 is a reply to message #678087] Sun, 10 November 2019 01:34 Go to previous messageGo to next message
John Watson
Messages: 8086
Registered: January 2010
Location: Global Village
Senior Member
Your code doesn't work because there is no column NOTE in TB2.
Re: problem to show result in join tables [message #678096 is a reply to message #678094] Sun, 10 November 2019 09:05 Go to previous messageGo to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member
Michel Cadot wrote on Sat, 09 November 2019 13:32

What is the clear specification of the problem?
Not what is your problem to solve it.

What is the specification of each line and for each line, of each column?
What "total" is total of?

only just need if the data in detail repated
only showing the data of column total only one time
with the data in column note showing completed
Re: problem to show result in join tables [message #678097 is a reply to message #678095] Sun, 10 November 2019 09:06 Go to previous messageGo to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member
John Watson wrote on Sun, 10 November 2019 01:34
Your code doesn't work because there is no column NOTE in TB2.
Sorry i forget added in the post but it in the table tb2
Re: problem to show result in join tables [message #678098 is a reply to message #678096] Sun, 10 November 2019 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
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

Re: problem to show result in join tables [message #678099 is a reply to message #678098] Sun, 10 November 2019 12:35 Go to previous messageGo to next message
hassan08
Messages: 122
Registered: June 2011
Location: egypt
Senior Member
Michel Cadot wrote on Sun, 10 November 2019 12:07
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.


yes my total is repeated in each row
and the primary key in master table is tr_no
and the tr_no in table tb2 referential to tb1
and exactly i need to show to only time
for per transactions addition to column note
the data stay without no change
Re: problem to show result in join tables [message #678100 is a reply to message #678099] Sun, 10 November 2019 14:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
yes my total is repeated in each row
This is not what your example shows, it shows your data are wrong as I listed.
Read again carefully the output of the table from your test case.

Re: problem to show result in join tables [message #678159 is a reply to message #678100] Wed, 13 November 2019 11:21 Go to previous message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Any feedback?

Previous Topic: Ajuda com procedure Type Pipilined
Next Topic: who called my procedure through a database link
Goto Forum:
  


Current Time: Mon Dec 16 01:07:48 CST 2019