Getting a min(date) AND max(date) AND their respective titles

Question

I have three tables that I would like to select from

Table 1 has a bunch of static information about a user like their idnumber, name, registration date

Table 2 has the idnumber of the user, course number, and the date they registered for the course

Table 3 has the course number, and the title of the course

I am trying to use one query that will select the columns mentioned in table 1, with the most recent course they registered (name and date registered) as well as their first course registered (name and date registered)

Here is what I came up with

SELECT u.idst, u.userid, u.firstname, u.lastname, u.email, u.register_date, 
    MIN(l.date_inscr) as mindate, MAX(l.date_inscr) as maxdate, lc.coursename
FROM table1 u,table3 lc
LEFT JOIN table2 l
ON l.idCourse = lc.idCourse
WHERE u.idst = 12787
AND u.idst = l.idUser

And this gives me everything i need, and the dates are correct but I have no idea how to display BOTH of the names of courses. The most recent and the first.

And help would be great.

Thanks!!!

Are you using MySQL 8.0? This can be done with [window functions](https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html). — Bill Karwin, Mar 05 '20 at 21:57
Why do you need to do this in one query? It's easier if you do two separate queries. — Bill Karwin, Mar 05 '20 at 21:58
@BillKarwin I am running a script that will be making api calls to over 100,000 users so I am trying to trim it down — Mat, Mar 05 '20 at 22:00

score 2 · Accepted Answer · answered Mar 05 '20 at 22:13

You can get your desired results by generating the min/max date_inscr for each user in a derived table and then joining that twice to table2 and table3, once to get each course name:

SELECT u.idst, u.userid, u.firstname, u.lastname, u.email, u.register_date,
       l.mindate, lc1.coursename as first_course,
       l.maxdate, lc2.coursename as latest_course
FROM table1 u
LEFT JOIN (SELECT idUser, MIN(date_inscr) AS mindate, MAX(date_inscr) AS maxdate
           FROM table2
           WHERE idUser = 12787
          ) l ON l.idUser = u.idst
LEFT JOIN table2 l1 ON l1.idUser = l.idUser AND l1.date_inscr = l.mindate
LEFT JOIN table3 lc1 ON lc1.idCourse = l1.idCourse
LEFT JOIN table2 l2 ON l2.idUser = l.idUser AND l2.date_inscr = l.maxdate
LEFT JOIN table3 lc2 ON lc2.idCourse = l2.idCourse

As @BillKarwin pointed out, this is more easily done using two separate queries.

I just wrote the *exact* same query, and then saw your answer. Cheers! — GMB, Mar 05 '20 at 22:32
Yes sometimes the other answers stop loading if you don't refresh... It's all good, glad you provided this query, which seems like the *right approach* to me. — GMB, Mar 05 '20 at 22:35

Getting a min(date) AND max(date) AND their respective titles

1 Answers1