CODEWITHSUNDEEP
sql
akevan
akevan

Reputation: 691

Selecting most recent record from multiple tables

Newbie to SQL, just stepped out of my comfort zone. I'm using MySQL in a WPF application.

I have three tables in my database.

Patients:

ID | Name | ...

Referrals:

ID | FK_Patient_ID | ...

Visits:

ID | FK_Referral_ID | Date | FollowUpDate | FollowUpInterval | ...

The 'FK' fields are foreign keys into the other tables. So a visit belongs to a referral, and a referral belongs to a patient.

I want the get the most recent visit for each patient (or referral, since you can't have a visit without a referral) and get the following:

patients.ID | patients.Name | visits.FollowUpDate | visits.FollowUpInterval

What I'm trying to do is get a list of patients who have missed their follow up visits.

Hopefully this is a no brainer for you SQL people out there...

Upvotes: 3

Views: 7407

Answers (3)

Kevin Ogoro
Kevin Ogoro

Reputation: 1127

I used the script as below on PostgeSQL and it worked!

SELECT p.ID, p.NAME, v.FollowUpDate, v.FollowUpInterval
FROM Patients p
    INNER JOIN Referrals r ON p.ID = r.FK_PatientID
    INNER JOIN Visits v ON ON r.ID = v.FK_Referral_ID
    INNER JOIN (SELECT r.FK_Patient_ID, MAX(v.Date) AS "Date"
                FROM Referrals r 
                INNER JOIN Visits v ON r.ID = v.FK_Referral_ID
                GROUP BY 
                r.FK_Patient_ID
                ) x ON p.ID = x.FK_Patient_ID AND v.Date = x.Date

Upvotes: 0

Dale M
Dale M

Reputation: 2473

SELECT   p1.ID
        ,p1.Name
        ,v1.FollowUpDate
        ,v1.FollowUpInterval
FROM    Patients p1
        INNER JOIN
        Referals r1 ON p1.ID=r1.FK_Patient_ID
        INNER JOIN
        Visits v1 ON r1.ID=v1.FK_Referral_ID
        INNER JOIN (
        SELECT  MAX(v.ID) AS ID
        FROM    Patients p
                INNER JOIN
                Referals r ON p.ID=r.FK_Patient_ID
                INNER JOIN
                Visits v ON r.ID=v.FK_Referral_ID
        GROUP BY p.ID) v2 ON v1.ID=v2.ID

Upvotes: 2

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

I'm not 100% sure if this works with MySQL, but here's one way that you could do it in SQL Server, and I think it's portable:

SELECT p.ID, p.NAME, v.FollowUpDate, v.FollowUpInterval
FROM Patients p
    JOIN Referrals r ON p.ID = r.FK_PatientID
    JOIN Visits v ON ON r.ID = v.FK_Referral_ID
    JOIN (SELECT r.FK_Patient_ID, MAX(v.Date) AS [Date]
            FROM Referrals r 
                JOIN Visits v ON r.ID = v.FK_Referral_ID
            GROUP BY r.FK_Patient_ID
         ) x ON p.ID = x.FK_Patient_ID
                AND v.Date = x.Date

Basically, you use a subquery to find the most recent visit by patient, and then join it back to your original tables to pull back the rows that match that value. This only works if there was one row with that date.

Upvotes: 1

Related Questions