Annduviste cerca, pero parece que no entiendes la idea del RIGHT JOIN, sobre todo porque no es la cláusula adecuada.
Entendamos que:
INNER JOIN juta dos tablas por medio de una relación obligatoria, es decir, devuelve sólo los registros donde existen efectivamente valores relacionados.
LEFT JOIN devuelve todo lo que está en la primera tabla, se cumpla o no la relación indicada, y donde no se cumple, en la segunda devuelve nulos.
RIGHT JOIN es su complementario, por lo que devuelve TODOS los valores de la SEGUNDA TABLA, cumplan o no con la relación. Pero eso implica que en la primera se devuelven NULL donde la relación no se cumple.
En base a eso, te imaginarás que estás intentando asignar valores existentes en la segunda tabla, a relaciones inexistentes en la primera.... lo que es imposible.
Fuera de eso, estás solapando alias entre la consulta base y la subconsulta, lo que llevará a MySQL a generar errores de parseo imposibles de resolver o generará asignaciones incorrectas.
De todos mdos, como dije, estás cerca. Yo lo probarías así:
De todos modos, como creo que ya te dijeron, es una muy mala práctica crear campos calculados en una base de datos relacional. Si los pusieras como solución de un examen de BBDD, te reprobarían sin más trámite.
Nunca se ponen porque son antiperformáticos a todos los niveles, y porque pueden generar información sucia o inconsistente.
¿Por qué?
Porque requieren constante actualización por procesos ad-hoc, lo que termina conspirando contra las necesidades de la aplicación. Y además no se necesitan, por cuanto la misma consulta que obtiene los datos puede hacer los cálculos necesarios
on the fly, con lo que siempre tienes la información actualizada.
Además, es mejor mantener información que pueda ser trazable históricamente, y un campo calculado no sirve para eso.
En definitiva, se transforman en datos-basura. En BBDD se los considera "vicios de programadores".