join question
-
I want to select several fields from 2 tables where each row in Table1 may map to 0 to n rows on Table2, but I only want to return a single row from Table2 where a date field is >= a date on Table1.
For example:
Select R_ID
, P.Field1
, Date1
, S.Field2
, S.Field3
from Table1 as P
Left outer join Table2 as S
on P.Field1 = S.Field1 and S.StartDate >= P.Date1
Order by Field1, S.StartDate
This returns all the rows on Table2 but I want only that row with the earliest S.StartDate
I tried to use a subquery with the result set sorted by StartDate asc and specify "Top1" but I couldn't get the syntax to work.
Any suggestions?
-
For problems like this, you generally need to join to a derived table (note, not a sub-query), where the derived table introduces a column that resolves to just 1 row. In your case, you want to introduce MIN(StartDate) as a date to join on:
In this example, I used a derived table named "dtEarliest"
Select R_ID
, P.Field1
, Date1
, S.Field2
, S.Field3
from Table1 as P
Left outer join
-- Join a derived table, to get the Earliest date for each Field1 value
(
Select S.Field1, Min(S.StartDate) As EarliestStartDate
From Table1 As P
Inner Join Table2 as S
on P.Field1 = S.Field1 and S.StartDate >= P.Date1
Group By S.Field1
) dtEarliest
On (dtEarliest.Field1 = P.Field1)
-- Joint to Table2, this time joining on both Field1 and the Date
-- so that there is only 1 row per Field1
Left Outer Join Table2 As S
On (dtEarliest.Field1 = S.Field1 And
dtEarliest.EarliestStartDate = s.StartDate)
Order by P.Field1, S.StartDate
-
Thanks a bunch! That worked. Now I have to work to figure out why it worked
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply