Comple x Query

[amerbaig]

I have following two tables

Table1
refcode CityCode
1240 2240
1340 2340
1440 2440

Table2
CityCode1 CityCode2 Distance
2240 2340 100
2340 2440 200

I want to get output like this

refcode1 RefCode2 Distance
1240 1340 100
1340 1440 200


How should I write SQL?

Regards
Amer

 

[cfsenorin]

Try this one:

select refcode1, refcode2, dist1
from (select a.refcode refcode1, distance dist1
from table1 a, table2 b
where a.citycode = b.citycode1) a,
(select a.refcode refcode2, distance dist2
from table1 a, table2 b
where a.citycode = b.citycode2) b
where a.dist1 = b.dist2;

 

[amerbaig]

Thanks it worked

 

[jn]

try this also..........

select b.refcode refcode1, c.refcode refcode2, a.distance
from table2 a, table1 b, table1 c
where a.citycode1 = b.citycode
and a.citycode2 = c.citycode ;