My SQL LEFT JOIN statement has multiple matches on the table on the right. I would only like to return a single match containing the next date

Question

My SQL statement:

SELECT c.*, s.followup FROM clients c LEFT JOIN scans s ON c.id=s.client_id

The scans table joins to the clients table on the client_id column. The scans table can have multiple entries for each client and has a followup column containing dates. I would like to return only the scan that has the date closest to today.

When I attempted this using a WHERE condition, it eliminated items from the left table that didn't have a followup date.

_date closest to today_ - for each user, or in total? A [mcve] is a great start when asking for SQL assistance. Also add a tag for the dbms you're using, when it comes to date/time many products have their own functions. — jarlh, Jan 27 '23 at 12:55
Take a few minutes to build out a small sample table on something like https://dbfiddle.uk/ — ClearlyClueless, Jan 27 '23 at 13:51
Does this answer your question? [Fetch the rows which have the Max value for a column for each distinct value of another column](https://stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of) — philipxy, Jan 28 '23 at 00:15

Drewby · Answer 1 · 2023-02-21T19:35:44.500

0

If you use a WHERE in a subquery, you can select the rows you want to include:

SELECT c.*, s.minfollowup 
FROM clients c 
LEFT JOIN (
        SELECT client_id, MIN(followup) minfollowup
        FROM scans 
        WHERE followup > CURDATE()
        GROUP BY client_id
    ) s 
    ON c.id=s.client_id

edited Feb 21 '23 at 19:35

answered Jan 27 '23 at 14:57

Drewby

23
5

I think this is very close to what I need, but I'm still getting an error that s.followup does not exist. I'll keep tweaking it, but thanks for pointing me in this direction! – Ivan Vasquez Jan 29 '23 at 15:42
@IvanVasquez Thanks for the note - I updated the query to use an alias. Maybe that will work for you. – Drewby Feb 21 '23 at 19:36

score 0 · Answer 2 · edited Jan 29 '23 at 23:29

0

SELECT c.*, s.min
FROM clients c 
LEFT JOIN (
    SELECT client_id, MIN(followup)
    FROM scans 
    WHERE followup > CURRENT_DATE
    GROUP BY client_id
) AS s 
ON c.id=s.client_id

The only change was s.followup to s.min in line 1.

edited Jan 29 '23 at 23:29

philipxy

14,867
6
39
83

answered Jan 29 '23 at 16:11

Ivan Vasquez

1
2

Plesae avoid social & meta commentary in posts. – philipxy Jan 29 '23 at 23:30

My SQL LEFT JOIN statement has multiple matches on the table on the right. I would only like to return a single match containing the next date

2 Answers2