MySQL INNER JOIN Empty Rows with Subqueries and Max Date

Question

I have 3 tables related by code, like this

I want to get the result of my 3 related tables by grouping the repeated rows according to update register dates. I have tried this to join them

SELECT
    stu.code,
    stu.name,
    stu.lastname,
    adr.address, 
    adr.phone,
    adr.register_date,
    uni.university,
    uni.degree,
    uni.register_date

FROM student stu

INNER JOIN 
    address adr
    ON adr.code = stu.code

INNER JOIN (
    SELECT code, MAX(register_date) reg_date
    FROM address
    GROUP BY code
) max_address
ON max_address.code = adr.code
AND max_address.reg_date = adr.register_date    

LEFT JOIN
    university uni
    ON uni.code = stu.code

LEFT JOIN (
    SELECT code, MAX(register_date) reg_date
    FROM university
    GROUP BY code
) max_uni
ON max_uni.code = uni.code
AND max_uni.reg_date = uni.register_date

This code returns ...

I just want to return the rows with the max register date (Address and University tables) per student which would be the ones with the red arrows, How could I do this even if some students doesn't have a row in the university table ?

As you can see, it is happening with "Brandon Walker", there isn't an 854 code in university table and it is displaying the NULL in those fields but is not grouping by the Olivia's and John's records because I'm using LEFT JOIN in order to bring those "NULL" records from Brandon.

If I use INNER JOIN it won't retrieve the Brandon Walker register at all

INNER JOIN
    university uni
    ON uni.code = stu.code

INNER JOIN (
    SELECT code, MAX(register_date) reg_date
    FROM university
    GROUP BY code
) max_uni
ON max_uni.code = uni.code
AND max_uni.reg_date = uni.register_date

To be more specific I want to return this in my query

Any suggestion of how can I achieve this ?

Answer 1

GROUP BY isn't very helpful when you need to retain row-associations after finding the MAX() value for a particular student.

What logically comes to mind for me because the students table contains unique student codes, is to select the student data then perform subqueries on the address and university tables to isolate the single row with the latest register_date before LEFT JOINing (which will allow the attachment of null rows) to the student table.

SQL (Demo)

SELECT
    a.code,
    a.name,
    a.lastname,
    b.address,
    b.phone,
    b.register_date AS addr_date,
    c.university,
    c.degree,
    c.register_date AS uni_date
FROM student a
LEFT JOIN (
  SELECT code, address, phone, register_date
  FROM address sub_b
  WHERE register_date = (
      SELECT MAX(register_date)
      FROM address
      WHERE code = sub_b.code
  )) b ON a.code = b.code
LEFT JOIN (
  SELECT code, university, degree, register_date
  FROM university sub_c
  WHERE register_date = (
      SELECT MAX(register_date)
      FROM university
      WHERE code = sub_c.code
  )) c ON a.code = c.code
ORDER BY a.code

Output:

| code | name    | lastname | address | phone | addr_date           | university | degree | uni_date            |
| ---- | ------- | -------- | ------- | ----- | ------------------- | ---------- | ------ | ------------------- |
| 853  | John    | White    | craFtur | 315   | 2016-10-10 07:00:01 | Bristol    | 145    | 2011-02-19 00:00:00 |
| 854  | Brandon | Walker   | cra 101 | 313   | 2005-11-04 06:00:00 | NULL       | NULL   | NULL                |
| 855  | Olivia  | Wright   | cll 26D | 310   | 2012-05-06 07:00:00 | Stanford   | 10025  | 2010-01-07 00:00:00 |