How to JOIN a table on an aggregate

Question

Issue Summary:

I need to join a single row to a table output based on an aggregate function, in this case of most recent corresponding record. Various other questions on this topic seem to work on the basis that both tables values are required (INNER JOIN, etc.) but in my case the aggregate needs to work on a LEFT JOIN table that is many times going to be NULL.

MySQL 5.7; Here is an couple of illistrative tables:

Tables

Core_data:
-----------

create table `core`
(
core_id int(8) unsigned auto_increment primary key,
some_name varchar(80) null,
some_data varchar(80) null,
some_values .... etc. 
)


Linked_data:
------------

create table `linked_data`
(
link_id smallint(6) unsigned auto_increment primary key,
core_id int(8) unsigned
data_date date,
some_linked_data_values varchar(80) null
)

I have a query dealing with dozens of tables. selecting 1 row from the Core table and selecting various LEFT JOIN data from dozens of other tables.

The illustrated linked data table has data that is dated, and the date is important, that only the most recent is returned.

Example Data:

linked_data
------------

link_id | core_id |  data_date  | data_value | ...
-------------------------------------------------
   1    |    2    |  2020-09-03 |  something | ...
   2    |    4    |  2019-07-29 |  whatever  | ...
   3    |    1    |  2017-11-09 |    yews    | ...
   4    |    4    |  2018-04-10 |    socks   | ...

I want to only join the row with core_id = 4 AND the maximum date value. How can I create this within the JOIN scenario; I can't put the MAX aggregate into the JOIN ... ON condition.

My Current SQL:

My SQL is something like this:

   SELECT ... many columns ..., 
   ld.data_value,
   ld.data_date,
   more.columns ... 
   FROM core
   LEFT JOIN table1 ON core.core_id = table1.core_id
   LEFT JOIN table2 ON core.core_id = table2.core_id
   LEFT JOIN table3 ON core.core_id = table3.core_id
   ... etc ...
   LEFT JOIN linked_data ld ON core.core_id = ld.core_id AND MAX(ld.data_date)
   WHERE ... core_id = value

One table I need only a result row that has the highest value of a column (data based), there is no reason for linked_data to hold any data so the LEFT JOIN may return NULL

Expected result:

For core_id = 4 I want to be able to output a single SQL row result containing linked_data.data_value = whatever . For core_id = 5 I want to be able to output the rest of the data but nothing from linked_data. table.

What Have I tried already?

This answer is noted as correct but is also noted that it will become very slow very quickly with larger amounts of data.
This answer put the qualifier in the WHERE clause, but there's no promise that linked_data will contain any result at all so, I can of course add further conditionals (check if into the WHERE clause here but I was hoping to avoid this.
This MySQL post has another possible solution but comments on this also state it is very slow (that may be user error on their part, I've not tested it yet).

I have also tried using a SELECT in the LEFT JOIN like so:

 SELECT ... many columns ..., 
 ld.data_value,
 ld.data_date,
 more.columns ... 
 FROM core
 LEFT JOIN table1 ON core.core_id = table1.core_id
 LEFT JOIN table2 ON core.core_id = table2.core_id
 LEFT JOIN table3 ON core.core_id = table3.core_id
 ... etc ...
 LEFT JOIN (
     SELECT linked_data FROM linked_data ldi WHERE core.core_id = ldi.core_id AND MAX(ldi.data_date) 
 ) as ld ON core.core_id = ldi.core_id
 WHERE ... core_id = value

Referenced from this Q&A

But this still tells me Aggregate calls are not allowed here

EDIT: I found why the aggregate wasn't allowed; a simple syntax mistake on my part; but I have put up a full answer to clarify this Q&A as I couldn't find any relative answers when I was searching, so this may be useful to someone.

If anyone has a more correct way of solving the original issue please share!

Martin · Accepted Answer

After writing out this whole question, I found that a simple syntax fix on my latest example SQL resolved the correct way to do this (and I've done this in the past the correct way but had not learnt it).

I note the lack of (or rather my inability to easily find) a clear definitive answer on the Interwebs to my query, so here's my methodology...

The correct way to do this type of aggregated join with respect to getting 0 or 1 results from the joined table is as follows:

LEFT JOIN and then wrap the subquery in brackets and set it as ... as per usual.
Put the aggregation function into the subquery ignoring the join criteria; the join criteria will be in the outer query ON ... section.
use ORDER BY in the sub query and then set the aggregate at that point.

So;

   SELECT ... many columns ..., 
   ld.data_value,
   ld.data_date,
   more.columns ... 
   FROM core
   LEFT JOIN table1 ON core.core_id = table1.core_id
   LEFT JOIN table2 ON core.core_id = table2.core_id
   LEFT JOIN table3 ON core.core_id = table3.core_id
   ... etc ...
   LEFT JOIN (
       SELECT linked_data FROM linked_data ldi WHERE optional = 1 ORDER BY MAX(ldi.data_date) 
   ) as ld ON core.core_id = ldi.core_id
   WHERE ... core_id = value

There is no need for a WHERE in the subquery but obviously if you do then it should be static and not need to reference the outer table, as that's done on the ON clause. I put in WHERE optional = 1 for familiarity.

The nature of LEFT JOIN is it will return 0 or 1 results so I find there's no need for much else.

If anyone has a better way of solving my original issue please let me know!

P.s> I have not efficiency tested this answer at all.