SQL subquery to join the most recent plan for a client-CodePudding

In MS Access 365, I have two tables (TClients & TPlans) that I am trying to combine into a single Query (QClientsExtended). Each client could have many or no associated entries on the TPlan list. In my final query, I would like it to list every client regardless of wether or not they have a plan, and give me the date and the details of the most recent plan, if there is one.

I've read all the relevant W3C reference pages, and looked at so many possible solutions, and i've struggled to turn them into something that works for this. It feels like it should be SO simple, I appreciate I'm probably missing a fundamental aspect of SQL coding.

TClients

ClientID    ClientFullName      ExternalAppts
1           Testy McTestFace    1
2           Clemence Closed     0
3           Nancy New Ref       3
4           Juan One Appt       0

TPlans

PlanID  ClientID    PlanDetails PlanDate
1       3           Plan 1      05-Dec-22
2       3           Plan 2      10-Dec-22
3       1           plan        10-Dec-22
4       4           nil         05-Dec-22

Qclients Extended

ClientID    ClientFullName      PlanDetails PlanDate    ExternalAppts
1           Testy McTestFace    Plan 2      10-Dec-22   1
2           Clemence Closed                             0
3           Nancy New Ref       plan        10-Dec-22   3
4           Juan One Appt       nil         05-Dec-22   0

CodePudding user response：

Try with a Left Join:

Select 
    TClients.ClientID,
    TClients.ClientFullName,
    TPlans.PlanDetails,
    TClients.PlanDate,
    TClients.ExternalAppts
From
    TClients
Left Join
    TPlans On TClient.ClientId = TPlans.ClientId
Order By
    TClients.ClientId