Subselects would probably work. As an alternative, here is a derived table method that should also work. I'm going to build this in stages so you can see how it works.
First, let's identify the max update_date for each ID in TableC.
Code:
[COLOR=blue]Select[/color] Id, [COLOR=#FF00FF]max[/color](Update_date) [COLOR=blue]As[/color] UpdateDate
[COLOR=blue]From[/color] TableC
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Id
When you copy/paste this to Query Analyzer, you'll see each id and whatever the max(update_date) is. Now, let's take this query and make it a derived table so that you can see how to get all of the details from TableC for the max date.
Code:
[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color] TableC
[COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
[!]Select Id, max(Update_date) As UpdateDate
From TableC
Group By Id[/!]
) [COLOR=blue]As[/color] A
[COLOR=blue]On[/color] Tablec.Id = A.Id
And TableC.update_date = A.UpdateDate
) [COLOR=blue]As[/color] TC
[COLOR=blue]On[/color] TableA.Id = TC.Id
Notice the part in red is the original query, but now it is considered a derived table. Derived tables must be surrounded with parenthesis and also have an alias (As TC). You can then treat this derived table as though it were a regular table. You should also notice that each ID has exactly 1 row in the output, and all of the data in the table is returned, but only where the data matches the max update_date.
Now, we can make this a derived table and join it to TableA so that we can get the data out of that table too. It would look like this...
Code:
[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color] TableA
[COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] (
[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color] TableC
[COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
[COLOR=blue]Select[/color] Id, [COLOR=#FF00FF]max[/color](Update_date) [COLOR=blue]As[/color] UpdateDate
[COLOR=blue]From[/color] TableC
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Id
) [COLOR=blue]As[/color] A
[COLOR=blue]On[/color] Tablec.Id = A.Id
And TableC.update_date = A.UpdateDate
) [COLOR=blue]As[/color] TC
[COLOR=blue]On[/color] TableA.Id = TC.Id
[COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] (
[COLOR=blue]Select[/color] *
[COLOR=blue]From[/color] TableB
[COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] (
[COLOR=blue]Select[/color] Id, [COLOR=#FF00FF]max[/color](Update_date) [COLOR=blue]As[/color] UpdateDate
[COLOR=blue]From[/color] TableB
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] Id
) [COLOR=blue]As[/color] B
[COLOR=blue]On[/color] Tablec.Id = B.Id
And TableB.update_date = B.UpdateDate
) [COLOR=blue]As[/color] TB
[COLOR=blue]On[/color] TableA.Id = TB.Id
Notice that there is code here for TableB also, but it is essentially the same as TableC.
Does this make sense?
-George
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom