combine two table records

[rizunb]

Hello People
I have 2 tables

table 1 has two columns id and top1, id is not unique
id top 1
1 test1
1 test2

table 2 has two columns id and top2, id is not unique
id top 2
1 test3
1 test4

how do i join both tables or do a query so that I get the folowing result

result :
id top1 top2
1 test1 test3
1 test2 test4

I hope you see what I mean
Thanks

 

[netw3rker]

to the best of my knowledge, you arent going to be able to achieve those results thorugh a query simply because any way you relate those 2 non unique key's you are going to generate 4 records. 1=1 every time there.

the best 2 options i think you can get are:

select table1.id, "table1.top 2", "table2.top 3"
from table1 inner join table2 on table1.id=table2.id

which will give you:
1 test1 test3
1 test1 test4
1 test2 test3
1 test2 test4

you might want to consider a union and grouping the output in whatever langauge you are using.. eg:

select id, 1 as topX, 'top 1' as myTop
from table1

union

select id, 2 as topX, 'top 2' as myTop
from table2

which will give you a simpler format to work with:

id topx myTop
1 1 test1
1 1 test2
1 2 test3
1 2 test4

hope this helps
-Chris