OraFAQ Forum: Reports & Discoverer » Join, one item with many

Home » Developer & Programmer » Reports & Discoverer » Join, one item with many

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Join, one item with many [message #601014]

Thu, 14 November 2013 10:31

amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia

Senior Member

Hello, every one

I have two tables,
table one has the primary key is beneficiary_number
table two has the foreign key is follower_number
I want to join them , I wrote this query

select w.beneficiary_number ,
w.card_number,
w.family_name,
w.full_name,
w.identification_number ,
w.id_issue ,
w.phone_number ,
w.important_ontes ,
f.full_name
from oracle_waqf w JOIN
oracle_followers f
ON (w.beneficiary_number = f.follower_number)
where (w.beneficiary_number = 5 );

Now, the normal report will show me every person in a page,,,,
the different is in the name of the follower name,,,
what I want is
I want this query to be displayed as the following:
the details will be given from oracle_waqf table,
and the followers will be all in a list,,,

I am using Oracle report 6i

thank you

Report message to a moderator

Re: Join, one item with many [message #601023 is a reply to message #601014]

Thu, 14 November 2013 13:59

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I'm not sure I understood what you want. Could you illustrate it, please? You can do that in Notepad (write an imaginary output) and display what you currently have and what kind of an output you'd like to have.

My lucky guess would be this: you need to create a new group; you'd do that by drawing new group's items out of the current group. Then go to Paper Layout editor and delete everything; run the Wizard, choose Group Left (or Above) layout style, follow Wizard's questions and that should do it. Of course, it will look ugly, but you can make it pretty in a matter of minutes.

Report message to a moderator

Re: Join, one item with many [message #601042 is a reply to message #601023]

Fri, 15 November 2013 00:42

amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia

Senior Member

alright,
let's say that we have two tables,
the father table and the children table
The father table has a primary key, Father_number,
in the children table the foreign key is , Father_number,
The connections between the tables is One to Many
so, it's for sure, a father can have more than one kid,
I want to show a report that gives me the father and his details , but the sons will be as list,
---------------------------------------------------------------------------------------------------
the Query bellow :

select w.beneficiary_number ,
w.card_number,
w.family_name,
w.full_name,
w.identification_number ,
w.id_issue ,
w.phone_number ,
w.important_ontes ,
f.full_name
from oracle_waqf w JOIN
oracle_followers f
ON (w.beneficiary_number = f.follower_number)
---------------------------------------------
will show it, but, it will show the following
------------------------------------------------------------------
father name father number kid name
Amjad***********************55***********************Hanan
Amjad***********************55***********************Sammar
Amjad***********************55***********************Zain
------------------------------------------------------------------------
but, I want it to appear as the following :

father name father number kid name
Amjad***********************55***********************Hanan
*****************************************************Sammar
*****************************************************Zain
--------------------------------------------------------------------------------
well, you suggested I have to put them in a group,
I think the best way is Group Left

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
now, I am trying to modify the report to show in a way I want

Thank you

[Updated on: Fri, 15 November 2013 00:43]

Report message to a moderator

Re: Join, one item with many [message #601045 is a reply to message #601042]

Fri, 15 November 2013 00:58

amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia

Senior Member

Also, I want each father to be displayed in a page

Report message to a moderator

Re: Join, one item with many [message #601054 is a reply to message #601045]

Fri, 15 November 2013 01:57

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Well, that would be a left group.

What do you mean by this:Quote:

I want each father to be displayed in a page

Do you mean fathers that don't have any children? If so, OUTER JOIN is what you need.

Report message to a moderator

Re: Join, one item with many [message #664480 is a reply to message #601054]

Thu, 20 July 2017 01:49

amjad_alahdal
Messages: 102
Registered: October 2013
Location: Saudi Arabia

Senior Member

Thank You. Problem solved.

Report message to a moderator

Previous Topic:	setting vertical elasticity overlapping on lines below it
Next Topic:	insert a pdf form

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 22:22:55 CST 2025