how to use join stage for joinig more than two tables
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 5
- Joined: Mon Jun 29, 2009 4:47 am
- Location: Bangalore
how to use join stage for joinig more than two tables
Hi,
I want to use Join Stage for joining three tables. Suppose I have 3 tables names respectively: A, B and C. Table A and B are to be joined on key column 'K1' and table B and C are joined on key column 'K2'. I have specified table B as intermediate, table A as left and table C as right, in the link ordering setting. Please help me to specify as to which key column of K1 and K2 is meant for which pair of tables that I want to join.
Pair1-> Left - Intermediate
Pair2-> Right - Intermediate
Kindly help me in this matter.
Thanks,
Sunil
I want to use Join Stage for joining three tables. Suppose I have 3 tables names respectively: A, B and C. Table A and B are to be joined on key column 'K1' and table B and C are joined on key column 'K2'. I have specified table B as intermediate, table A as left and table C as right, in the link ordering setting. Please help me to specify as to which key column of K1 and K2 is meant for which pair of tables that I want to join.
Pair1-> Left - Intermediate
Pair2-> Right - Intermediate
Kindly help me in this matter.
Thanks,
Sunil
-
- Premium Member
- Posts: 20
- Joined: Mon Dec 19, 2005 10:00 pm
- Location: UK
-
- Participant
- Posts: 94
- Joined: Wed May 08, 2002 8:44 am
- Location: Germany
- Contact:
Re: how to use join stage for joinig more than two tables
In one join stage you can join multiple links on the same keys only. In your case you have to use 2 joins: join A and B on K1 and then repartion, sort and join B and C on K2.
Klaus
Klaus
-
- Premium Member
- Posts: 151
- Joined: Fri Feb 13, 2009 4:19 pm
-
- Participant
- Posts: 5
- Joined: Mon Jun 29, 2009 4:47 am
- Location: Bangalore
Re: how to use join stage for joinig more than two tables
Thanks KlausKlaus Schaefer wrote:In one join stage you can join multiple links on the same keys only. In your case you have to use 2 joins: join A and B on K1 and then repartion, sort and join B and C on K2.
Klaus
![Smile :)](./images/smilies/icon_smile.gif)
-
Thanks & Regards
Sunil Agrawal
Thanks & Regards
Sunil Agrawal
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The short answer to the original question is that the Join stage supports more than two input links if required. They are called "Left", "Intermediate" and "Right" in the Input Link Ordering dialog. I believe that joins are performed pairwise, beginning from the left.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
More than two inputs.
In SQL it would look something like
SELECT columns FROM tableA,tableB,tableC
WHERE tableA.key = tableB.key AND tableA.key = tableC.key
tableB is the intemediate here.
In SQL it would look something like
SELECT columns FROM tableA,tableB,tableC
WHERE tableA.key = tableB.key AND tableA.key = tableC.key
tableB is the intemediate 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.