Ok, I'm going to try to explain it better.
table: inspections
__________________________________________________________________
| inspection_id | inspection_timestamp | inspection_score | account_id_FK |
----------------------------------------------------------------------------
| 1 | 11111111111 | 8 | 1 |
----------------------------------------------------------------------------
| 2 | 22222222222 | 6 | 1 |
----------------------------------------------------------------------------
| 3 | 11111111111 | 5 | 2 |
----------------------------------------------------------------------------
table: accounts
___________________________
| account_id | account_name |
-------------------------------
| 1 | Adam's Auto |
-------------------------------
| 2 | Bob's Bakery |
-------------------------------
| 3 | Carl's Cats |
-------------------------------
| 3 | David's Ducks|
-------------------------------
I want the records for ALL accounts, matched with their highest inspection score and the correlated inspection date, if available.
If they don't have any inspections then their info should still be in the result set.
Example desired result set for above tables:
3 results:
_____________________________________________________________________________________________
| account_id | account_name | inspection_id | inspection_timestamp | inspection_score | account_id_FK |
----------------------------------------------------------------------------------------------------------
| 1 | Adam's Auto | 2 | 22222222222 | 6 | 1 |
----------------------------------------------------------------------------------------------------------
| 2 | Bob's Bakery | 2 | 11111111111 | 5 | 2 |
----------------------------------------------------------------------------------------------------------
| 3 | Carl's Cats | null | null | null | null |
----------------------------------------------------------------------------------------------------------
| 4 | David's Ducks| null | null | null | null |
----------------------------------------------------------------------------------------------------------
Note that while Adam's Auto has two inspections, only the most recent inspection is included in the data set (although it has a lower score).
The closest I've gotten is everything above EXCEPT that the inspection score doesn't match the inspection date, so I get the most recent inspection date with some other date's inspection score, which obviously is worthless.
I've been doing this for hours and I'm going crazy, help please .
Thanks,
chuck Pearce