Hi all, I hope someone could help me out with this sql select statement. I will try my best to describe it.

My current sql statement is:
SELECT *
FROM road_status rs, roadclosure rc, status, problem, sector,
(SELECT max(statustime) as maxtime, roadclosureid
FROM road_status
GROUP BY roadclosureid) maxresults
WHERE rc.roadclosureid = maxresults.roadclosureid
AND rs.statustime= maxresults.maxtime
AND rc.problemid = problem.problemid
AND rc.sectorid = sector.sectorid
AND rs.roadclosureid = rc.roadclosureid
AND rs.statusid = status.statusid
AND status.statusid <> 2

That all works fine above and just to explain that "nested" select statement... Basically have a table that keeps a record of all the status changes and I always want the latest status to return with the roadclosure... So that all works...

i have another table called "notification" with the folloing fields:
"notificationid" (primary, AI)
"roadclosureid" (FK points back to "roadclosure.roadclosureid")
"agencyid"
"notificationtime"

And another table "agency" with fields:
"agencyid" (primary, AI)
"agencyname"

What I want to return with the same select query above is the agencyname for the latest "notificationtime" BUT... If there is no notification it should still pull them up just as null values. So what I think I need is somehow adding in a: LEFT JOIN notification ON notification.roadclosureid = rc.roadclosureid and then also link in the agency table so the actual "agencyname" is returned instead of just its ID... I also think I would need another one of those nested selects in order to find the latest "notificationtime" if one exists and if not again, i still just want null values returned and my PHP will deal with that accordingly.

I have tried a few ways. I havent kept track of them but one way would not return any results if there were no records in the "notification" table probably because i just did a regular join and also if there were multiple "notification" records for the same roadclosureid then it would return multiple lines for each one basically because it wasn't limiting it to the max(notificationtime) maybe because i was missing a WHERE statement

I know I could probably just write out a second select statement to get the information I am looking for but i would prefer to have it all come back in the one resultset so I don't have to worry about dealing with 2 separate array's worth of results and keeping them linked together.

Thanks in advance for any help you could give. If you need more information about the other tables let me know...