I have 2 tables.
One full of stuff related to apple trees
The other keeps a log of what happens to the apple trees.
So when I change the status of an apple try i make a row in the log saying the status changed for this apple tree from to and when and by who.
SO what I want to do is create a VIEW of the most recent log entry for each apple tree thus creating a LastModified table. I know I could add a field "ModifiedWhen" & "ModifiedBy" but since this is a made up scenario that best describes my real much more complex scenario. I'm interested in way I've described more then how to best do this simplistic example.
Ok now to put all this visually....
ID | Status | Color
1 | Fully Grown | Red
2 | Just Planted | Green
3 | Seedling | Green
4 | Seedling | Red
ID | R_A_ID | Who | When | Notes
1 | 1 | John | 2007-05-06 | Planted
2 | 1 | Tim | 2007-05-12 | Seedling Now
3 | 2 | John | 2007-05-13 | Just Planted
4 | 1 | Tim | 2007-05-14 | Got some leaves
5 | 3 | Tim | 2007-05-15 | Planted
6 | 4 | John | 2007-05-16 | Planted
7 | 1 | Tim | 2007-05-16 | eh, its a Red one now, thought it was green...
8 | 1 | Tim | 2007-05-17 | Fully Grown apple try now - yum!
9 | 3 | Tim | 2007-05-19 | Seedling Now
10| 4 | John | 2007-05-20 | Seedling Now
ApplesID | Who | When
1 | Tim | 2007-05-17
2 | John | 2007-05-13
3 | Tim | 2007-05-19
4 | John | 2007-05-20
There that sums up pretty good what I'm trying to do. Just select the most recent entry from the Log table as it relates to the Apples.
My query building so far has got me all the log results as a outer join, then I try limiting it and I end up with 1 result and thats it....