Row to Row join

[j8slo]

I'm working with MS SQL 2000, I need to join tables A and B to give me table C. How would I write such a query?

A B C
----- ----- ---------
| a | | 1 | | a | 1 |
| b | | 2 | | b | 2 |
| c | | 3 | | c | 3 |
| d | | 4 | | d | 4 |
----- ----- ---------

I tried doing some research but just wasn't able to find anything. Any help would be appreciated.

 

[jbenson001]

What columns do you have in each of the tables you want to join?

 

[j8slo]

Well this is just theoretical as what I am doing is quite complicated and more than I can explain here.
Ok, lets say table A has a column called 'alpha' and B has column called 'numeric'. Let's also say there is a pseudo-value 'row' which gives the row number in that table. What I am trying to do then is something like this.

select alpha, numeric
from A, B
where A.row = B.row

 

[jbenson001]

You will have to have some sort of Key in each table that relates to each other for the join to take place on. Also, will it be a one to one or one to many relationship?

 

[j8slo]

That's exactly my problem... I don't have a key in the table to go off of. I was hoping MS SQL had something like 'rownum' in Oracle or there was some sort of work around.
This is a one to one relationship.

 

[amrita418]

Sql server 2005 has row_number function but not 2000.

Anyways, one way to get around this is adding identity columns to your tables. Each row in the table will then have a unique value.

If you cannot play around with the structure of your original tables then load the data into temp tables.

Regards,
AA