MySQL query to get latest value from child tables

Question

I have a table for clients and three tables of related details as seen from this Fiddle.

What I would like to do is get the clients' data and their related rows for the latest contact date.

I would like to create a view from that query and be able to filter by operator id. I am always getting a wrong number of rows to what I expect.

Below is my query, can someone help me figure out why it returns incorrect results?

select c.*,cs.client_status,cn.client_note,cd.contact_date from clienti c
left join client_status cs on c.id = cs.client
left join client_notes cn on c.id = cn.client
left join client_contact_date cd on c.id = cd.client
where 
(
    (
        `cd`.`contact_date` = (select max(`client_contact_date`.`contact_date`)  from `client_contact_date` where `client_contact_date`.`client` = `c`.`id`)
    ) 
    AND 
    (
        `cn`.`mod_time` = (select max(`client_notes`.`mod_time`) from `client_notes` where `client_notes`.`client` = `c`.`id`)
    ) 
    AND 
    (
        `cs`.`mod_time` = (select max(`client_status`.`mod_time`) from `client_status` where `client_status`.`client` = `c`.`id`)
    )
)

Show us some of your code, then we might be able to tell you what's wrong — Osuwariboy, Jul 21 '15 at 15:39
Also, are you looking for every client note of the latest date, or the latest client note of that date? — AdamMc331, Jul 21 '15 at 15:56

score 2 · Accepted Answer · edited May 23 '17 at 12:29

I would suggest you break this up into smaller pieces and then begin to put the joins together.

At the core of everything that you want is the latest contact_date/status/note for each client. You can get each of those using aggregation:

SELECT client, MAX(contact_date) AS latestContact
FROM client_contact_date
GROUP BY client;

SELECT client, MAX(mod_time) AS latestNote
FROM client_notes
GROUP BY client;

SELECT client, MAX(mod_time) AS latestStatus
FROM client_status
GROUP BY client;

The first query can easily be outer joined to the client table to get the client information since there should only be one row for each client. For the last two of those subqueries, you'll have to join back to the original tables (notes, status) to get the information like this, because the row must be matched by time as well:

SELECT n.client, n.client_note, n.mod_time
FROM client_notes n
JOIN(
  SELECT client, MAX(mod_time) AS latestNote
  FROM client_notes
  GROUP BY client) t ON t.client = n.client AND t.latestNote = n.mod_time;

SELECT s.client, s.client_status, s.mod_time
FROM client_status s
JOIN(
  SELECT client, MAX(mod_time) AS latestStatus
  FROM client_status
  GROUP BY client) t ON t.client = s.client AND t.latestStatus = s.mod_time;

Once you have all of those things, you can use outer joins to bring the latest rows of information together:

SELECT c.id, c.clientName, c.operators, cd.latestContact, cn.client_note, cn.latestNote, cs.client_status, cs.latestStatus
FROM clienti c
LEFT JOIN(
  SELECT client, MAX(contact_date) AS latestContact
  FROM client_contact_date
  GROUP BY client) cd ON cd.client = c.id
LEFT JOIN(
  SELECT n.client, n.client_note, n.mod_time AS latestNote
  FROM client_notes n
  JOIN(
    SELECT client, MAX(mod_time) AS latestNote
    FROM client_notes
    GROUP BY client) t ON t.client = n.client AND t.latestNote = n.mod_time) cn ON cn.client = c.id
LEFT JOIN(
  SELECT s.client, s.client_status, s.mod_time AS latestStatus
  FROM client_status s
  JOIN(
    SELECT client, MAX(mod_time) AS latestStatus
    FROM client_status
    GROUP BY client) t ON t.client = s.client AND t.latestStatus = s.mod_time) cs ON cs.client = c.id;

I would like to make one comment though. I see that you are storing operators as a list. This breaks normalization, and is generally a very bad idea. For me information, see Is storing a delimited list in a database column really that bad?

Here is an SQL Fiddle example with my query.

you beat me few minutes :) I just select max value with subquery :) here is my [Fiddle](http://sqlfiddle.com/#!9/3b07c/17) Hope that is what he is trying to do xD GL! — Aleksandar Miladinovic, Jul 21 '15 at 16:28
just tried. It's ok! I added some field and fitted on my tables names. I would like to create a view out of it, but as the query has subqueries in from clause, it cannot be done. Can you suggest me another option? — Aptivus, Jul 22 '15 at 15:13
I solved by making a lot of views and then a global view that queries the others. — Aptivus, Jul 22 '15 at 19:21

MySQL query to get latest value from child tables

1 Answers1