I have three tables: friends, locations, friend_location
friend_location is a join table to allow the many-to-many relationship between friends and locations, so the tables will look something like this:
Friends
ID | Name
1 | Jerry
2 | Nelson
3 | Paul
location
ID | Date | Lat | Lon
1 | 2012-03-01 | 34.3 | 67.3
2 | 2011-04-03 | 45.3 | 49.3
3 | 2012-05-03 | 32.2 | 107.2
friend_location
Friend_ID | Location_id
1 | 2
2 | 1
3 | 3
2 | 2
What I would like to do is get the latest location for each friend.
Results
ID | Friend | Last Know Location | last know date
1 | Jerry | 45.3 , 49.3 | 2011-04-03
2 | Nelson | 34.3 , 67.3 | 2012-03-01
3 | Paul | 32.2 , 107.2 | 2012-05-03
This is what I have tried after looking at various examples, but it returns to many results and is not correct:
select f.id , f.name , last_known_date
from friends f, (
select distinct fl.friend_id as friend_id, fl.location_id as location_id, m.date as last_known_date
from friend_location fl
inner join (
select location.id as id, max(date) as date
from location
group by location.id
) m
on fl.location_id=m.id
) as y
where f.id=y.friend_id
Any suggestions would be greatly appreciated.
You could do something like this:
SELECT f.id, f.name, last_known_date, l.Lat, L.Lon
from Friends f
join
(
select f.id, MAX(l.Date) as last_known_date
from Friends f
JOIN Friend_Location fl on f.ID = fl.Friend_ID
JOIN Location l on l.ID = fl.Location_ID
GROUP BY f.id
) FLMax
on FLMax.id = f.id
join Friend_Location fl on fl.friend_ID = f.ID
join Location l on fl.location_ID = l.ID AND l.Date = FLMax.Last_Known_Date
Basically your problem is that you are grouping by location.id which will give you all of the locations because the ID is unique.
This only works if a friend can only be at 1 location at any 1 time.
Gregs query looks correct to me, I came up with similar one (see below). However current db schema doesn't handle situation when two friends visited same locations in different order. For me it seems that Date column should be in friend_location table, not location. However if it's not then query is:
SELECT F.ID, F.Name AS Friend, L.Lat, L.Lon, L.Date
FROM
(
SELECT MAX(L.date) AS max_date, F.ID
FROM Friends F
JOIN friend_location FL ON F.ID=FL.Friend_ID
JOIN Location L ON L.ID=FL.Location_id
GROUP BY F.ID
) AS X
JOIN Friends F ON X.ID=F.ID
JOIN friend_location FL ON F.ID=FL.Friend_ID
JOIN location L ON L.ID=FL.Location_id AND L.Date=X.max_date
Your data layout is a bit strange because the date is in the location table. So, the following retrieves the latest date for each friend:
select fl.friend_id, max(l.date) as maxdate
from friend_location fl
location l join
on fl.location_id = l.location_id
Now, let's just join back in the information to this query:
select f.*, maxdate, l.*
from (select fl.friend_id, max(l.date) as maxdate
from friend_location fl
JOIN location l
on fl.location_id = l.id
group by fl.friend_id
) flmax join
friends f
on flmax.friend_id = f.id
join location l
on l.date = flmax.maxdate
This will work, assuming that the locations don't have duplicate dates. If they do, the query is a bit more complicated. Can we make this assumption?
You can use:
SELECT
a.*,
CONCAT(d.Lat, ' , ', d.Lon) AS last_known_location,
d.Date AS last_known_date
FROM
friends a
JOIN
(
SELECT a.Friend_ID, MAX(b.Date) AS maxdate
FROM friend_location a
JOIN location b ON a.Location_id = b.ID
GROUP BY a.Friend_ID
) b ON a.ID = b.Friend_ID
JOIN
friend_location c ON b.Friend_ID = c.Friend_ID
JOIN
location d ON c.Location_id = d.ID AND b.maxdate = d.Date
