Iterating 1 row at a time with massive amounts of links/joins

Question

Ok, basically what is needed is a way to have row numbers while using a lot of joins and having where clauses using these rownumbers.

such as something like

select ADDRESS.ADDRESS FROM ADDRESS 
INNER JOIN WORKHISTORY ON WORKHISTORY.ADDRESSRID=ADDRESS.ADDRESSRID
INNER JOIN PERSON ON PERSON.PERSONRID=WORKHISTORY.PERSONRID
WHERE PERSONRID=<some number> AND WORKHISTORY.ROWNUMBER=1

ROWNUMBER needs to be generated for this query on that one table though. So that if we want to access the second WORKHISTORY record's address, we could just go WORKHISTORY.ROWNUMBER=2 and if say we had two address's that matched, we could cycle through the addresses for one WORKHISTORY record using ADDRESS.ROWNUMBER=1 and ADDRESS.ROWNUMBER=2

This should be capable of being an automatically generated query. Thus, there could be more than 10 inner joins in order to get to the relevant table, and we need to be able to cycle through each table's record independently of the rest of the tables..

I'm aware there is the RANK and ROWNUMBER functions, but I'm not seeing how it will work for me because of all the inner joins

note: in this example query, ROWNUMBER should be automatically generated! It should never be stored in the actual table

Answer 1

Can you use a temp table?

I ask because you can write the code like this:

select a.field1, b.field2, c.field3, identity (int, 1,1)  as TableRownumber into #temp
from table1 a 
join table2 b on a.table1id = b.table1id
join table3 c on b.table2id = c.table2id

select * from #temp where ...