I have two tables. One with People, the other with contacts. I can join the tables via People (PersonID) and Contacts (PersonID) columns.

In the Contacts Table, I have ContactDate.

I want to select all People with not contact within 7 days.

How? I'm getting contacts rows which aren't the most recent rows when I join the tables and do a where clause saying ContactDate > 7 days.

I need the date of my the most recent contact I guess.

But ultimately I only want People returned who have no contact date within 7 days.

    I'm gonna guess too: something involving GROUP BY and a WHERE clause involving MAX on the contact date.

      Well I've done this the long way, loop through all people in my People DB. Inside this loop do another search and get a single row from Contacts where PersonID is equal to the parent loop ID. Sort this result by ContactDate desc and limit to1.

      Then do an if query, if inside 7 days do nothing else do what I need.

        That sounds like you have 1 + as many queries as there are People records. At scale, that isn't as good as doing it in a single query, with something like what Weedpacket suggested.

          This works in SQLite:

          SELECT People.* FROM People JOIN Contacts ON People.PersonID=Contacts.PersonID
    GROUP BY People.PersonID HAVING MAX(ContactDate)<'2019-05-12';

            You could also do a sub-query:

            select
  People.*, 
  (select max(ContactDate) from Contacts where people_id = People.people_id) as last_contact
from People
where last_contact < :desired_date

            This way you'd get nulls for anyone who has not contact data yet, if that is desirable? (Otherwise you could add something to the where clause to exclude a null last_contact.)

            PS: Not saying this is the "best" or "correct" way, just something that came to mind. 🙂

              Write a Reply...