Jioning two tables

[longs]

Hi Guys

I want to find out how to get the following result?

Table 1
col1 Col2
123 100
123 200

Table 2
c1 c2
123 189
123 190


The result I need is as following:

123 100 189
123 200 190

Can you help me with the query for this one.

I tried to normal join it give following

123 100 189
123 100 190
123 200 189
123 200 190


Thanks for your help.

Longs

 

[jimirvine]

You would need to post the logic as to why the data should join like that. i.e. why would 189 be associated with 100 and not 200?

 

[Dagon]

One option would be to use case statements e.g.

select t1.col1, t1.col2, t2.c2
from table1 t1, table2 t2
where case when t1.col2 = 100 then 189 else 190 end = t2.c2

The feasibility of this would depend on the number of translations to be done. If there are a lot, then you would be better to create an intermediate table which translates the 100 to 189 etc e.g.

create table trans 
(t1_key number, t2_key number);

insert into trans values (100, 189);
insert into trans values (200, 190);

select t1.col1, t1.col2, t2.c2
from table1 t1, table2 t2, trans tr
where tr.t1_key = t1.col2
and   tr.t2_key = t2.c2