I request your help to improve an SQL query.
There are times when I have a table I want to relate several foreign fields to a primary field of another table, but the problem is that there is more than 1 field with a foreign key.
Currently, what is done is to do the inner join 2 times to the same table:
SELECT E.nombre AS Nombre, U1.nombre AS 'Integrante 1', U2.nombre AS 'Integrante 2' FROM equipo AS E INNER JOIN usuario AS U1 ON E.usuario1_id = U1.id INNER JOIN usuario AS U2 ON E.usuario2_id = U2.id
The question is whether the same result can be obtained in a more optimal way without having to do the inner join 'n' times to the same table.
Thank you in advance for your help.