How to access parent column from a subquery within a join

Question

I'm trying to left join the second table useri_ban based on the users' ids, with the extra condition: useri_ban.start_ban = max_start.

In order for me to calculate max_start, I have to run the following subquery:

(SELECT MAX(ub.start_ban) AS max_start, user_id FROM useri_ban ub WHERE ub.user_id = useri.id)

Furthermore, in order to add max_start to every row, I need to inner join this subquery's result into the main result. However, it seems that once I apply that join, the subquery is no longer able to access useri.id.

What am I doing wrong?

SELECT
    useri.id as id,
    useri.email as email,
    useri_ban.warning_type_id as warning_type_id,
    useri_ban.type as type,
    useri.created_at AS created_at
FROM `useri`

inner join 
(SELECT MAX(ub.start_ban) AS max_start, user_id FROM useri_ban ub WHERE ub.user_id = useri.id) `temp`
     on `useri`.`id` = `temp`.`user_id`

left join `useri_ban` on `useri_ban`.`user_id` = `useri`.`id` and `useri_ban`.`start_ban` = `max_start`

Answer 1

Does this solve your problem? You need GROUP BY in the inner query instead of another join.

SELECT useri.id, useri.email, maxQuery.maxStartBan
    FROM useri  
    INNER JOIN
    (
    SELECT useri_ban.user_id ubid, MAX(useri_ban.startban) maxStartBan
        FROM useri_ban 
            GROUP BY useri_ban.user_id
    ) AS maxQuery
        ON maxQuery.ubid = useri.id;