MySQL - How to use a correlated subquery inside a JOIN

Question

I have two tables, Objects and Events.

Objects is structured like:

ID | Password
-------------
0  | aaaa
1  | bbbb

Events is structured like:

Object | Date       | Type
--------------------------
0      | 2020-06-01 | 0
0      | 2020-06-02 | 1

What I want to achieve is: for each ID, get the last Type of event associated. In the example above, Object 0 would be associated to 1, since 1 was the Type of the last event on 2020-06-02. Also, whenever an Object doesn't have any Events, associate 1 to it.

I tried to order the events for each Object, so that I could then join my Object information using the query:

SELECT ID, IFNULL(e.Type, 1)
FROM objects o
LEFT JOIN (
    SELECT e.Object, e.Date, e.Type
    FROM events e
    WHERE e.Object = o.ID
    ORDER BY e.Date DESC
    LIMIT 1
) AS e ON e.Object = o.ID

It does not work since o.ID is unknown, but I really can't think of any other solutions. Therefore my question is: how can I use an attribute of the outside table inside a condition for the joined table?

Please let me know if anything is unclear, thanks.

Answer 1

Use NOT EXISTS in Events to return only the last row by Date:

SELECT ID, IFNULL(e.Type, 1) Type
FROM Objects o
LEFT JOIN (
    SELECT e.Object, e.Type
    FROM Events e
    WHERE NOT EXISTS (
      SELECT 1 FROM Events
      WHERE Object = e.Object AND Date > e.Date 
    ) 
) AS e ON e.Object = o.ID

or:

SELECT 
  o.ID, 
  IFNULL((SELECT e.Type FROM Events e WHERE e.Object = o.ID ORDER BY e.Date DESC LIMIT 1), 1) Type
FROM Objects o

See the demo.
Results:

> ID | Type
> -: | ---:
>  0 |    1
>  1 |    1