Check MySQL counts to 0 with Joins

Question

Check MySQL counts to 0 with Joins

Navigation

#1 by (0 votes)

1

I have these four tables related to each other (ignore the relationships that go upwards).

I will use a query so that you can understand the problem, which is complex:

SELECT u.nombre, COUNT(fr.id_tipologia), t.nombre 
FROM tipologias AS t 
INNER JOIN fr 
  ON t.id = fr.id_tipologia 
INNER JOIN encargos AS e 
  ON fr.id_encargo = e.id 
INNER JOIN usuarios AS u 
  ON e.cod_usuario = u.cod_usuario 
GROUP BY u.nombre, fr.id_tipologia

Reading the query would be something like: Tell me how many types and with what name each user is assigned.

This is the following:

For now all correct, the data in essence are what I need, the issue is that these data are to display them in a graph, so I need the null of relationships for data ordering issues when interpreting them in the graphic.

Specifically, the "question" I want to ask the database would be: What typologies are not assigned to each user? That is, the answer to that query would be something like this:

+------------------+------------------------+------------------+
|      nombre      | COUNT(fr.id_tipologia) |      nombre      |
+------------------+------------------------+------------------+
| Desarrollo       | 29                     | Plaza de parking |
+------------------+------------------------+------------------+
| Desarrollo       | 22                     | Vivienda         |
+------------------+------------------------+------------------+
| Desarrollo       | 4                      | Oficina          |
+------------------+------------------------+------------------+
| Desarrollo       | 4                      | Local            |
+------------------+------------------------+------------------+
| Nombre de Testeo | 0                      | Plaza de parking |
+------------------+------------------------+------------------+
| Nombre de Testeo | 1                      | Vivienda         |
+------------------+------------------------+------------------+
| Nombre de Testeo | 0                      | Oficina          |
+------------------+------------------------+------------------+
| Nombre de Testeo | 0                      | Local            |
+------------------+------------------------+------------------+

I have not managed to get it out with the left and the right join, and the outer joins can not be used in MySQL I understand.

I hope you can help me, thank you very much!

I also add the results with LEFT JOIN:

SELECT u.nombre, COUNT(fr.id_tipologia), t.nombre 
FROM tipologias AS t 
LEFT JOIN fr 
    ON t.id = fr.id_tipologia 
LEFT JOIN encargos AS e 
    ON fr.id_encargo = e.id 
LEFT JOIN usuarios AS u 
    ON e.cod_usuario = u.cod_usuario 
GROUP BY u.nombre, fr.id_tipologia

mysql join

asked by SrMelian 28.08.2018 в 10:20

source

1 answer

Error when I try to assign a value to a key in a dict Python-Django Login with arrangement in Android Studio

score 0 · Answer 1

If we divide the problem in two. You already have all those records that have an accountant, we only need all those whose counter is zero.

Starting from a baseline where all the records with value 0 are, which we can easily find with:

select u.Nombre usuarioNombre, 0 contador, t.nombre tipologiaNombre
  from usuarios u
       cross join tipologias t

To achieve the final result I can think of several ways to achieve this, one of them could be to make a union of both queries in a derived table, to finally make a sum ().

something along the lines of:

select usuarioNombre, sum(contador) Contador, tipologiaNombre
  from (
        select u.Nombre usuarioNombre, 0 contador, t.nombre tipologiaNombre
          from usuarios u
               cross join tipologias t
        union all
        select u.nombre, COUNT(fr.id_tipologia), t.nombre
          from tipologias AS t
               inner join fr on t.id = fr.id_tipologia
               inner join encargos AS e on fr.id_encargo = e.id
               inner join usuarios AS u   ON e.cod_usuario = u.cod_usuario
         group by u.nombre, fr.id_tipologia
) q1
group by usuarioNombre, tipologiaNombre