Double Select 1

[Genimuse]

My SQL skills are pretty basic, but thanks to the help of some folks here I managed to get a query that works for my situation. Unfortunately the situation changed, and I really have no idea how to go about rewriting this to make it work.

The basics: Two tables, with a one-to-many relationship, like this (most of the extra fields removed for simplicity):

[b]MedicarePatients[/b]
ID         -- Primary Key, autonumber
FirstName
LastName
Hold       -- True/False boolean

[b]MedicareCheckups[/b]
ID         -- Foreign Key, to MedicarePatients' primary
Checkup    -- DateTime
Scheduled  -- True/False boolean, scheduled checkup vs. actual checkup

My previous need was to create a select where I got all of the items in MedicarePatients, along with their latest (in time) checkup (with some ordering). The select (thanks so much to the folks here) I'm using now looks like this:

SELECT P.ID, P.LastName, P.FirstName, P.Hold, D.LastDate, P.Notes 
FROM MedicarePatients P LEFT JOIN (
SELECT ID, Max(Checkup) As LastDate FROM MedicareCheckups GROUP BY ID
) D ON P.ID = D.ID ORDER BY P.Hold DESC

(some additional order bys removed).

But here's the new requirement: from the MedicareCheckups table I need both the latest record where Scheduled is false and the latest one where Scheduled as true, hopefully all in one select. Obviously it's easy for me to add a WHERE clause to the sub-select to get one or the other, but I can't figure out how to join the MedicarePatients table with the MedicareCheckups table twice (or whatever I need to do).

Any help would be greatly appreciated.

 

[PHV]

SELECT P.ID, P.LastName, P.FirstName, P.Hold, D.LastDate, D.Scheduled, P.Notes
FROM MedicarePatients P LEFT JOIN (
SELECT ID, Scheduled, Max(Checkup) As LastDate FROM MedicareCheckups GROUP BY ID, Scheduled
) D ON P.ID = D.ID ORDER BY P.Hold DESC


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Genimuse]

Wow, that was simple! I've forgotten more than I ever learned about GROUPing, apparently. Thanks so much!

Is there a reasonably simple way to get the dates as separate rows, one with Scheduled as False and one as True, or is that a bit too convoluted?

 

[Genimuse]

Ah, nevermind, I realized a way I can deal with the data in multiple rows. Thanks again.