Inner Join doesn't work well

Question

I'm trying to get the town (Localidad) from referees(ArbPrin, ArbAux, Anotador, Crono, Op24). I make the query and the result isn't correct. Per example if I only have ArbPrin and Anotador it returns the town of ArbPrin for all the referees. If ArbPrin is from Malaga and Anotador is from Torremolinos, the result is Malaga for all the referees.

This is the query:

SELECT L1.Nombre AS'LocPrin', L2.Nombre AS'LocAux', L3.Nombre AS'LocAn', L4.Nombre AS'LocCro', L5.Nombre AS'LocOp' FROM PARTIDO INNER JOIN EQUIPO_ARBITRAL A1 ON PARTIDO.ArbPrin=A1.Codigo_arbitro INNER JOIN EQUIPO_ARBITRAL A2 ON PARTIDO.ArbPrin=A2.Codigo_arbitro INNER JOIN EQUIPO_ARBITRAL A3 ON PARTIDO.ArbPrin=A3.Codigo_arbitro INNER JOIN EQUIPO_ARBITRAL A4 ON PARTIDO.ArbPrin=A4.Codigo_arbitro INNER JOIN EQUIPO_ARBITRAL A5 ON PARTIDO.ArbPrin=A5.Codigo_arbitro INNER JOIN LOCALIDAD L1 ON A1.Cod_localidad=L1.Codigo_localidad INNER JOIN LOCALIDAD L2 ON A2.Cod_localidad=L2.Codigo_localidad INNER JOIN LOCALIDAD L3 ON A3.Cod_localidad=L3.Codigo_localidad INNER JOIN LOCALIDAD L4 ON A4.Cod_localidad=L4.Codigo_localidad INNER JOIN LOCALIDAD L5 ON A5.Cod_localidad=L5.Codigo_localidad;

Here an example of the result. If I have the follows referees:

ArbPrin: Malaga, ArbAux: Torremolinos, Anotador: Benalmadena

The result is the next:

LocPrin LocAux LocAn LocCro LocOp ----------------------------------------------- Malaga Malaga Malaga Malaga Malaga

The result I want is the next:

LocPrin LocAux LocAn LocCro LocOp -------------------------------------------------------- Malaga Torremolinos Benalmadena

check this out: sqlfiddle.com/#!2/13c6f2/2 I just changed how you join with PARTIDO. You were always joining on the first field which in your fiddle contains 1, so it is logical you get the same name every where. I Joined once on the first field, then on the second field, then on the 3rd field. — CodeBird
– CodeBird, Commented Apr 29, 2014 at 8:01

CodeBird · Accepted Answer · 2014-04-29 08:05:38Z

Here you go, I think this is what you want:

SELECT L1.Nombre AS'LocPrin', L2.Nombre AS'LocAux', L3.Nombre AS'LocAn', L4.Nombre AS'LocCro', L5.Nombre AS'LocOp' FROM PARTIDO LEFT JOIN EQUIPO_ARBITRAL A1 ON PARTIDO.ArbPrin=A1.Codigo_arbitro LEFT JOIN EQUIPO_ARBITRAL A2 ON PARTIDO.ArbAux=A2.Codigo_arbitro LEFT JOIN EQUIPO_ARBITRAL A3 ON PARTIDO.Anotador=A3.Codigo_arbitro LEFT JOIN EQUIPO_ARBITRAL A4 ON PARTIDO.Crono=A4.Codigo_arbitro LEFT JOIN EQUIPO_ARBITRAL A5 ON PARTIDO.Op24=A5.Codigo_arbitro LEFT JOIN LOCALIDAD L1 ON A1.Cod_localidad=L1.Codigo_localidad LEFT JOIN LOCALIDAD L2 ON A2.Cod_localidad=L2.Codigo_localidad LEFT JOIN LOCALIDAD L3 ON A3.Cod_localidad=L3.Codigo_localidad LEFT JOIN LOCALIDAD L4 ON A4.Cod_localidad=L4.Codigo_localidad LEFT JOIN LOCALIDAD L5 ON A5.Cod_localidad=L5.Codigo_localidad;

Demo

The only thing I'd add to this is that, where a join column in PARTIDO is non-nullable (assuming there are some), the corresponding join could be made FULL rather than LEFT. This might gain a bit of performance. +1
@halfer feel free to edit my answer, or answer and I'll delete mine and learn something new. thanks for the +1
No - it's only a minor issue, and you've done all the hard work anyway :)

Collectives™ on Stack Overflow

Inner Join doesn't work well

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related