Help w/ JOIN - Require 1 row from ony-to-many for each unique id

This site is best viewed in a modern browser with JavaScript enabled.

Help w/ JOIN - Require 1 row from ony-to-many for each unique id

SoreGums

MYSQL 5.0

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....

[code]

Apples

ID | Status | Color

ApplesLoG

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

ApplesModified (View)

ApplesID | Who | When

1 | Tim | 2007-05-17
2 | John | 2007-05-13
3 | Tim | 2007-05-19
4 | John | 2007-05-20
[/code]
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....

Please help

bubbisthedog

What does your query look like so far?

bubbisthedog

This may do what you're looking for:

select ta.max_appid,  Who, ta.max_date
from ApplesLoG
join
(select R_A_ID as max_appid, max(When) as max_date
from ApplesLoG
group by R_A_ID) ta
on ta.max_appid = ApplesLoG.R_A_ID
and ta.max_date = ApplesLoG.When

Note that if there are multiple entries for the same ID on the same date, that the query should return all of them.

SoreGums

bubbisthedog wrote:
Note that if there are multiple entries for the same ID on the same date, that the query should return all of them.

Thanks, Will give that a go - yeah the date is actually a TIMESTAMP field, I just made it date so as to save me typing more in the example

I knew would have to involve sub queries, just my sub query skillz are lacking...

I could do this problem in a for loop in the program of course but the whole point of having a proper dbms is kinda to off load some of this to it

mattyblah

nm queston answered.

SoreGums

MYSQL 5.0 as mentioned at the very top - i read the sticky - it said mention this