one to many relationship in joins
Moderators: chulett, rschirm, roy
one to many relationship in joins
Hi
I have table ABC and need to join on DEF.
relation ship on ABC and DEF is one to many.
if my key column for join is COLUMN_TEST.
suppose COLUMN_TEST in ABC table has a value 123
and same COLUMN_TEST in DEF may have multiple records with value 123.
Can some please let me know how to acomplish this task.
ABC has 10 records after my join with DEF it have 100 or 1000....records.
Thanks
Suri
I have table ABC and need to join on DEF.
relation ship on ABC and DEF is one to many.
if my key column for join is COLUMN_TEST.
suppose COLUMN_TEST in ABC table has a value 123
and same COLUMN_TEST in DEF may have multiple records with value 123.
Can some please let me know how to acomplish this task.
ABC has 10 records after my join with DEF it have 100 or 1000....records.
Thanks
Suri
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
What you seek to do is a common or garden variety inner join, on the join key column whose value is 123 in your example. Where are you experiencing difficulty?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Thanks Ray for your quick responce.
I doing the left outter join. I have 1 to many relationships from left to right. How can I accomplish this in the job ( Do I need to Pivot the records).
This job is for delta loads
In my left table I will get nearly 60,000 records every day.
If I pivot this then I will have more then million records.
I trying to come up with some logic here.
Thanks
Suri
I doing the left outter join. I have 1 to many relationships from left to right. How can I accomplish this in the job ( Do I need to Pivot the records).
This job is for delta loads
In my left table I will get nearly 60,000 records every day.
If I pivot this then I will have more then million records.
I trying to come up with some logic here.
Thanks
Suri
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
No. All you need is an inner join. Nothing else is required. Inner join will give the correct number of rows.
Left outer join will give additional rows with only NULLs from the right input. This is not what you require, according to the specification you have posted here.
Left outer join will give additional rows with only NULLs from the right input. This is not what you require, according to the specification you have posted here.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: one to many relationship in joins
hello, i am not aware of parallel Extender, but hope their will be something similar for server edition, stream records and reference..
so you use your child table in stream records and parent table in reference.
so you use your child table in stream records and parent table in reference.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
Why don't you understand what Ray says
The INNER JOIN is the join for you.
Let me explain.
say your source is
ABC
===
Col1 Colx
======
1 Me
2 You
DEF
===
Col1 Col2
======
1 A
1 B
1 C
2 D
2 E
2 F
And if you have a simple INNER JOIN on Col1. then the o/p will be:
and have Col1,Colx from the left i/p link and Col2 from the right i/p link mapped to the o/p
This will be your result
Col1 Colx Col2
==========
1 Me A
1 Me B
1 Me C
2 You D
2 You E
2 You F
It can't be clearer than this.
The INNER JOIN is the join for you.
Let me explain.
say your source is
ABC
===
Col1 Colx
======
1 Me
2 You
DEF
===
Col1 Col2
======
1 A
1 B
1 C
2 D
2 E
2 F
And if you have a simple INNER JOIN on Col1. then the o/p will be:
and have Col1,Colx from the left i/p link and Col2 from the right i/p link mapped to the o/p
This will be your result
Col1 Colx Col2
==========
1 Me A
1 Me B
1 Me C
2 You D
2 You E
2 You F
It can't be clearer than this.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
You would want to use a Left Outer Join only when you want the records which don't have a match in the right link to be sent to the output.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>