INNER JOIN 1 primary field to several foreign fields in the same table

Navigation
3

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.

Something like this:

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

Leaving something like this:

Example:

  

link

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.

    
asked by Jose El Junior 12.10.2018 в 21:12
source

2 answers

4

Good afternoon Jose El Junior, I think your relationships are not normalized, they should be something like this:

So your inner join would look like this: 

SELECT 
    USU.nombre AS USUARIO,
    EQ.nombre AS EQUIPO
FROM USUARIO AS USU
INNER JOIN EQUIPO_USUARIO AS EUS ON USU.id = EUS.id_usuario
INNER JOIN EQUIPO AS EQ ON EUS.id_equipo = EQ.id
    
answered by 12.10.2018 в 21:27
1

Your query can be solved perfectly without using a single join.

To do this, we are going to make a subquery in each column that you want to add 

SELECT 
    E.nombre AS Nombre, 
    (select nombre from usuario where usuario1_id = id ) as 'Integrante 1',
    (select nombre from usuario where usuario2_id = id ) as 'Integrante 2'
FROM equipo AS E
    
answered by 12.10.2018 в 21:48
How to execute a transaction within a Command The mysterious message "example"