How to join
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 92
- Joined: Wed Mar 05, 2008 4:09 am
- Location: chennai
- Contact:
How to join
Please let me know how to join to two tables/files with out common column.
Approachs made:
Techinque1:adding a dummy column on both side and giving column extended properties algorithm=cycle and value=1 and increment=0 than it wil lead to cartesion product but we wil not be getting the required result
Techinque2:making all the metadata same in both table and using funnel stage to join both tables but the draw back is,Suppose if the tables is having more column then there it wil lead to complex for matching both sides and even we have to delete the data from the extra column using constraints.
please let me how to achieve or correct me if there is wrong in my approach.
Approachs made:
Techinque1:adding a dummy column on both side and giving column extended properties algorithm=cycle and value=1 and increment=0 than it wil lead to cartesion product but we wil not be getting the required result
Techinque2:making all the metadata same in both table and using funnel stage to join both tables but the draw back is,Suppose if the tables is having more column then there it wil lead to complex for matching both sides and even we have to delete the data from the extra column using constraints.
please let me how to achieve or correct me if there is wrong in my approach.
pradeep.v
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 92
- Joined: Wed Mar 05, 2008 4:09 am
- Location: chennai
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 92
- Joined: Wed Mar 05, 2008 4:09 am
- Location: chennai
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 92
- Joined: Wed Mar 05, 2008 4:09 am
- Location: chennai
- Contact:
Suppose there are two tables(A1,A2) with out common column,I want to fetch one column From A2 and join two tables with existing column of the A1 and map all the column to target.and if suppose a1 is having 17,500 records and A2 is having 2500 records,I have insert that 2500 records of col.A2 in to target table with all the column of A1 with record count of 17,500ray.wurlod wrote:So what is "the desired result"? Give a brief example of rows from inputs and expected output. ...
please how to achieve that
pradeep.v
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 92
- Joined: Wed Mar 05, 2008 4:09 am
- Location: chennai
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That doesn't help.
Show us some examples of data on both inputs, together with what you expect to get on the output.
Also show us what you have tried, and explain more fully what you mean by "does not work".
![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
Show us some examples of data on both inputs, together with what you expect to get on the output.
Also show us what you have tried, and explain more fully what you mean by "does not work".
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.
----------------Output rows?pradeep_nov18 wrote:Suppose there are two tables(A1,A2) with out common column,I want to fetch one column From A2 and join two tables with existing column of the A1 and map all the column to target.and if suppose a1 is having 17,500 records and A2 is having 2500 records,I have insert that 2500 records of col.A2 in to target table with all the column of A1 with record count of 17,500ray.wurlod wrote:So what is "the desired result"? Give a brief example of rows from inputs and expected output. ...
please how to achieve that
2500 17500 43750000
2500 17500 2500
2500 17500 17500
2500 17500 20000
2500 17500 15000
Which way??
Thanks
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 92
- Joined: Wed Mar 05, 2008 4:09 am
- Location: chennai
- Contact:
Thanks Ray,ray.wurlod wrote:Cartesian product.
Inner join or left outer join.
Right outer join.
UNION ALL.
Difference (sometimes called "minus"). ...
accordling to your solution for the example,I got wat i have perform right outer join.but the output(ex:2500 as below) and it should be left outer join cannn't be inner join right
![Confused :?](./images/smilies/icon_confused.gif)
pradeep.v
-
- Participant
- Posts: 92
- Joined: Wed Mar 05, 2008 4:09 am
- Location: chennai
- Contact:
Thanks Ray,ray.wurlod wrote:Cartesian product.
Inner join or left outer join.
Right outer join.
UNION ALL.
Difference (sometimes called "minus"). ...
accordling to your solution for the example,I got wat i have perform right outer join.but the output(ex:2500 as below) and it should be left outer join cannn't be inner join right
![Confused :?](./images/smilies/icon_confused.gif)
pradeep.v
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
An inner join (those rows that exist in both sets) can never be the same as a Cartesian product (all rows from the right input joined to every row from the left input). If the left input contains N rows and the right input contains M rows, then an inner join will return the smaller of N and M rows, whereas a Cartesian product will return N*M rows.
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.