SQL How to join 2 tables with or without-CodePudding

I have two tables, one with persons and one with contact moments. What I want is to join every person with their maximum contact moment based on the contact date. See the image below.

I am able to join every person on their maximum contact date where there is a contact moment (so I get the data back for John and Frank in the example above). However I cannot get the persons without a contact moment to show up as well (Bill and Kevin). For that I need help.

What I have done so far is the following:

SELECT 
    Person.Id,
    Person.Name,
    Contact.Id
    
FROM
    Person,
    Contact
    RIGHT JOIN (
        SELECT      Contact.PersonId, 
                    MAX(Contact.Date) AS MaxDate
        FROM        Contact
        GROUP BY    Contact.PersonId
    ) GroupedContact ON 
        Contact.PersonId = GroupedContact.PersonId AND 
        Contact.Date = GroupedContact.MaxDate
        
WHERE
    Person.Id = Contact.PersonId

I know that the WHERE here causes some of the issue, but if I remove it entirely, my query won't stop running and I get a timeout. Can anyone help me, as I cannot figure it out.

CodePudding user response：

Remove the additional unnecessary join to contact:

SELECT 
    Person.Id,
    Person.Name,
    GroupedContact.MaxDate as outputCol

FROM
    Person
    LEFT JOIN (
        SELECT      Contact.PersonId, 
                    MAX(Contact.Date) AS MaxDate
        FROM        Contact
        GROUP BY    Contact.PersonId
    ) GroupedContact ON 
        Person.PersonId = GroupedContact.PersonId

A few notes: Never mix implicit and explicit joins. Always use explicit joins. You need here a LEFT join which means -> keep all the records from the LEFT tables and those that match from the RIGHT table.

CodePudding user response：

You need LEFT join instead of RIGHT and extra join to contacts is not needed:

SELECT 
    Person.Id,
    Person.Name,
    GroupedContact.MaxDate
    
FROM Person
LEFT JOIN 
(
    SELECT      Contact.PersonId, 
                    MAX(Contact.Date) AS MaxDate
    FROM        Contact
    GROUP BY    Contact.PersonId
) GroupedContact 
  ON Person.Id = GroupedContact.PersonId