to avoid cross join while doing inner join

[atksamy]

I have 2 tables like which i would like to query to form a new table.

table 1

     number         type serial index
    1000001      613    3               1
    1000001      613    3               1
    1000001      613    3               1
    1000001      613    3               1
    1000001      613    4               1
    1000001      613    3               1
table 2

        number         type serial  index
        1000001      613        3               2
        1000001      613        3               3
        1000001      613        3               4
        1000001      613        3               4
        1000001      613        4               2
        1000001      613        3               1

i am looking for a query which gives a resultant of indexes and number like this and adds teh index and give s the result

output table

number                index
        1000001     3
        1000001     4 
        1000001     5 
        1000001     5 
        1000001     3
        1000001     2

I want to prevent cross join here i cant use where clause because the numbers are same in both tables only the index varies. how can i execute this one to one and not one to many

[NeoPa]

I expect the tables can be joined in a suitable way. However, I really don't follow what you are using to determine which records match and which do not. Certainly, the matching criteria are not even displayed in your required output (which is likely to make it confusing to anyone that sees it).

[atksamy]

Actually this is a bizaare query but there is no condition i mean if i use where clause using the number then it will be a cross join which i dont want. i only want the first line of table 1 to be processed with first line of table 2 and then second line of table 1 with second line of table 2 and so on... to process the index values

[NeoPa]

In that case this is not really database processing as such. It can be done in SQL if there is a field in the table which reflects the order required. Otherwise it can only be done in code.

This sounds to me very strongly that the design of your database needs some serious looking into. Check out Normalisation and Table structures for further info on design whys and wherefores.