help in joining two tables

[pandeesh]

Hi ,

I need your help in the below scenario:

I have two tables.

One of my table 'table1' contains the below data:

Code: Select all

Name,Value
------------
12A,1
12B,1
12C,1

Table2 contains the below data:

Code: Select all

value,result
------------
1,12
1,24
1,56
1,423
1,32
1,3

I need to join based on value field.

My expected result is:

Code: Select all

NAME,VALUE,RESULT
-------------------------
12A,1,12
12B,1,24
12C,1,56
12D,1,423
12E,1,32
12F,1,3

Depends on the number of records in the second table,we need to append A to Z at the end of the name field. The number of records will not exceed more than 26. How we can achieve this?


Thanks

[ray.wurlod]

Code: Select all

Char(64+rownumberingroup)

assuming you're counting from 1.

[pandeesh]

Thanks Ray!

Now there is an change in my requirement.

If you wish i will post as separate topic:

My input tables contains the below records:

Code: Select all

table1:

Name,id,value
----------------
A1,1,100
A2,1,200
B1,2,400
B2,2,300
C1,3,100

table2:

id,value
----------
1,30
1,70
1,100
1,200
2,300
2,100
2,200
2,50
2,50
3,50
3,50

The result i want is:

Code: Select all

Name,id,value
---------------
A1,1,30
A2,1,70
A1,1,100
A2,1,200
B1,2,300
B2,2,100
B1,2,200
B2,2,50
B1,2,50
c1,3,50
C1,3,50

Assume that for each id, the number of records in table 2 will be equal or more than the number of records in table1 for that particular id.

e.g:

for the id value 1,

No of records in table1:2
No of records in table2:4

So, the name values should be distributed in a cyclic manner till the last record reaches.

Please help me to achieve this.
Thanks

[SURA]

Make a full outer join and do what ever you want in the transformer...


DS User

[pandeesh]

Make a full outer join and do what ever you want in the transformer...

But full outer join gives the records sucha s:

Code: Select all

A1,2,200
A1,2,100
A1,2,300
A2,2,200
A2,2,100
A2,2,300

I want to avoid those records.

All the A* names should correspond to only the id '1' values.
All the B* names should correspond to only id '2' values.

Thanks

[SURA]

Hi pandeesh

This is what you are expecting?

Code: Select all

A1,100,1,70
A1,100,1,100
A1,100,1,200
A1,100,1,30
A2,200,1,70
A2,200,1,100
A2,200,1,200
A2,200,1,30
B1,400,2,50
B1,400,2,100
B1,400,2,300
B1,400,2,200
B1,400,2,50
B2,300,2,50
B2,300,2,100
B2,300,2,300
B2,300,2,200
B2,300,2,50
C1,100,3,50
C1,100,3,50

[pandeesh]

No.

i am expecting :

Code: Select all

A1,100,1,30 
A2,200,1,70 
A1,100,1,100 
A2,200,1,200 
B1,400,2,300 
B2,300,2,100 
B1,400,2,200 
B2,300,2,50 
B1,400,2,50 
c1,100,3,50 
C1,100,3,50 

[pandeesh]

Please let me know if you need any more information on the logic

Thanks,

[ray.wurlod]

This seems to me to be a simple inner join on id, possibly followed by a sort. Make sure you get the partitioning right, though.

[pandeesh]

Ray,

Simple inner join on id results in 20 records.
But as per my requirement, there should be only 11 records for that sample data.

thanks

[ray.wurlod]

Are your data partitioned on ID ?

[pandeesh]

No Ray!,
It's not partitioned.

Thanks

[SURA]

Pandeesh

Still i can't understand properly what is your need is!

You replied to Ray, you need only 11 rows.

How / why to decide which row should select and which should reject?

What is the business rule for that?

DS User

[kamtammystuff]

Is your data in table1 has maximum 2 records for a id?

[kamtammystuff]

Is your data in table1 has maximum 2 records for a id?

If the answer to the above question is yes then generate a column say key and assign values to it as shown below

table1:

Name,id,value,key
----------------
A1,1,100,1
A2,1,200,2
B1,2,400,1
B2,2,300,2
C1,3,100,1

table2:

id,value,key
----------
1,30,1
1,70,2
1,100,1
1,200,2
2,300,1
2,100,2
2,200,1
2,50,2
2,50,1
3,50,1
3,50,2

Now join the above 2 inputs on the columns id and key.

Try this and let me know if it works.