Double LEFT JOIN to a table does not work when one field is NULL and the other does not

0

For days I have been thinking about the following question:

SELECT U.Usuario AS Nombre, S.Nombre AS Sombrero, A.Nombre AS Arma, Z.Nombre AS Zona, R.Nivel, rangoRollo(R.Honor, R.ID_Usuario) AS Rango
              FROM Usuarios AS U
                  INNER JOIN Rollos AS R ON U.ID = R.ID_Usuario
                  INNER JOIN Zonas AS Z ON R.ID_Zona = Z.ID
                  LEFT JOIN Rollos_Equipables AS REA ON R.ID_Usuario = REA.ID_Rollo
                  LEFT JOIN Rollos_Equipables AS RES ON R.ID_Usuario = RES.ID_Rollo
                  LEFT JOIN Equipables AS A ON REA.ID_Equipable = A.ID
                  LEFT JOIN Equipables AS S ON RES.ID_Equipable = S.ID
              WHERE U.ID = ? AND
                  (REA.Equipada IS NULL OR REA.Equipada) AND
                  (RES.Equipada IS NULL OR RES.Equipada) AND
                  (A.Tipo IS NULL OR A.Tipo = 'A') AND
                  (S.Tipo IS NULL OR S.Tipo = 'S');
  • When the character (aka Rollo) has no weapon and hat, the query returns the fields correctly, indicating the character's weapon and hat.
  • When the character has as much weapon as a hat, the query works again correctly, returning both the weapon and the hat in the appropriate columns.

The problem appears when one of the two is NULL but the other has value. I guess the problem is the WHERE but I can not find a solution. Any advice on this?

Thanks in advance.

    
asked by Ézhor Malkávar 10.03.2018 в 19:20
source

1 answer

0

In the end I managed to solve it using two subqueries.

It is not the best method because it is more expensive, but it works. I would be happy to know of an alternative using INNER JOIN if someone knows.

Thanks to the community in the same way.

    
answered by 10.03.2018 в 20:15