joins

i have two tables each table having 2 column
table1 table2
eid ename eid ename
1 A 3 C
2 B 4 D

i shld use a select query to get the following output

eid1 eid2
1 3
2 4

there is no relation btw the 2 tables except that eid column of both tables r of same data type
the count of eid column in both table are also same

can a blind join be donecross join?|||Originally posted by bhargavi

can a blind join be done

what do u mean by a blind join?

if u just do
select table1.eid,table2.eid from table1,table2
will form a cross join and generate four record set result.|||if i establish a cross join i will have an output like

eid1 eid2
1 3
1 4
2 3
2 4

this is not wat i want
i have to select eid from first table and eid from second table
and dispaly those 2 columns as the same

select eid from table1
output:
1
2
select eid from table2
output:
3
4

some select query to combine both these outputs and provide an output
like

column1 column2
1 3
2 4

all these in a single select query|||you could run two separate queries, one on each table, saving the results into two temp tables, each of which has an identity column

then join the temp tables based on the identity column values

make it a full outer join in case on table has more rows than the other

you could also do it in one query without temp tables by using ranking queries to generate derived tables

here you can have this, i couldn't quite get it to work on some test rows i created...

select dt1.rank
, dt1.eid
, dt1.ename
, dt2.rank
, dt2.eid
, dt2.ename
from (
select (select count(*) from t1)
- count(*) + 1 as rank
, t1.eid
, t1.ename
from table1 as t1
, table1 as t2
where t1.eid <= t2.eid
group
by t1.eid
, t1.ename
) as dt1
full outer
join (
select (select count(*) from t1)
- count(*) + 1 as rank
, t1.eid
, t1.ename
from table2 as t1
, table2 as t2
where t1.eid <= t2.eid
group
by t1.eid
, t1.ename
) as dt2
on dt1.rank = dt2.rank
order
by dt1.rank
, dt2.rank