What is the best way to join one single line out of many?

Question

MariaDB

Good afternoon, ladies and gentlemen.
There is a request. I won't give the whole story, just the important points.

SELECT * FROM tbl1 LEFT JOIN tbl2 USING(field1) WHERE tbl2.field2 > 0;

The problem is that in the table between tbl1 and tbl2 there is a one-to-many relationship, and you need to extract only one row, which has the latest date value.
The request of the form below can not be offered, unfortunately, it greatly squanders performance in our situation. If there are no more productive / concise options - write.

SELECT * FROM tbl1
LEFT JOIN tbl2 ON tbl2.pk = (SELECT pk FROM tbl2 WHERE tbl2.field1 = tbl1.field1 ORDER BY ... LIMIT 1)
WHERE tbl2.field2 > 0

Answer 1

Of course, it squanders, you use a correlating subquery (in a query nested in tbl2, a selection from tbl1) ....

SELECT * FROM tbl1
    LEFT JOIN tbl2
    ON tbl2.pk = (SELECT pk FROM tbl2 WHERE tbl2.field1 = tbl1.field1 ORDER BY ... LIMIT 1)
    WHERE tbl2.field2 > 0

The description is strange, I don't see the date here.. is it pk?
It’s easier to drop the code without a description and ask to optimize it, I’ll take it that way, that’s why:

SELECT * FROM tbl1
    LEFT JOIN tbl2
    ON tbl2.field1 = tbl1.field1
    WHERE tbl2.field2 > 0
    ORDER BY tbl2.pk DESK LIMIT 1

Answer 2

Answered a similar question here.
The solution was:

SELECT *
FROM main m
LEFT JOIN extra e1 ON m.id = e1.main_id
LEFT JOIN extra e2 ON e1.main_id = e2.main_id AND e2.date > e1.date
WHERE e2.date IS NULL AND e1.status = 1;

I think the point is clear