Joining or Combining Two Tables

[escuro19]

Hi,

I spend alot of time trying to work this out but failed so please could someone help.

Data:
(this table the intday varies depending on the person)

Table1
Person | intDay
1 | 22
1 | 23
1 | 24
1 | 25
2 | 14
2 | 15
2 | 16
2 | 17

Table2
(this data is fixed, its like a scale)

+day | -day
8 | -7
9 | -6
10 | -5
11 | -4
12 | -3
13 | -2
15 | -1
n | n

This is what i want the output to be:

Person | intDay | +day | -day
1 | 22 | 8 | -7
1 | 23 | 9 | -6
1 | 24 |10 | -5
1 | 25 | 11 | -4
2 | 14 | 8 | -7
2 | 15 | 9 | -6
2 | 16 | 10 | -5
2 | 17 |11 | -4

So basically matching table1 with table2, but for each person the table2 matches resets. And also is it possible to stop matching once the Person's intday stops.

Thankyou for your help

 

[pjm]

select t1ranked.person, t1ranked.intday, t2ranked.[+day], t2ranked.[-day]
from

(SELECT 1+(select count (*) from table1 as t1 where t1.person = table1.person and t1.intday<table1.intday) AS t1Rank, Table1.Person, Table1.Intday
FROM Table1) as t1ranked,

(SELECT 1+(select count(*) from table2 as t2 where t2.[+day]<table2.[+day]) AS t2rank, Table2.[+day], Table2.[-day]
FROM Table2) as t2ranked

where
t1ranked.t1rank = t2ranked.t2rank;

... but I don't think it will perform well with large data sets!

HTH
pjm

 

[BigRed1212]

If you aren't too far established, you need to look at better database design. Actually, in my opinion you need to look regardless. A primary key in the first table and a keyed relationship between intDay values and +day values would go a long way.