Home / Questions / 55238
Accepted
JuankGlezz
Asked: 2020-03-15 08:11:50 +0800 CST 2020-03-15 08:11:50 +0800 CST

Avoid duplicate records and account for them

  • 772

I have a query in which I get a list of companies

SELECT S.FileB64 AS ImgB64, E.* FROM dbempresas E
    LEFT JOIN systorage S ON S.id = E.Logo_100 
    ORDER BY E.Nombre;

RESULTADO:
id |     RFC      |           Nombre
---+--------------+----------------------------------
14 | AAA010101AAA | ACCEM SERVICIOS EMPRESARIALES SC
15 | BBB010101BBB | CORPORACION ACME
16 | CCC010101CCC | INDUSTRIAS KORD

So far so good, but I want to add how many branches there are per company , giving me a result like this:

id |     RFC      |           Nombre                    |  Sucursales
---+--------------+-------------------------------------+--------------
14 | AAA010101AAA | ACCEM SERVICIOS EMPRESARIALES SC    |    2
15 | BBB010101BBB | CORPORACION ACME                    |    1
16 | CCC010101CCC | INDUSTRIAS KORD                     |    1

To try to achieve the above I have done this:

SELECT S.FileB64 AS ImgB64, ES.id, E.* FROM dbempresas E
    LEFT JOIN systorage S ON S.id = E.Logo_100 
    LEFT JOIN dbempresas_sucursales ES ON ES.IdEmpresa = E.id
    ORDER BY E.Nombre;

RESULTADO:
id |     RFC      |           Nombre                    |  ES.id
---+--------------+-------------------------------------+--------------
14 | AAA010101AAA | ACCEM SERVICIOS EMPRESARIALES SC    |    13
14 | AAA010101AAA | ACCEM SERVICIOS EMPRESARIALES SC    |    16
15 | BBB010101BBB | CORPORACION ACME                    |    17
16 | CCC010101CCC | INDUSTRIAS KORD                     |    18

I know that COUNT()I can count the records but I have no idea how to do it in the same query. How can I prevent idthe company from repeating itself and give me the result I want?

These are the tables in which I do the queries:

+------------------------------+   +-------------------------------+
|          dbEmpresas          |   |     dbEmpresas_Sucursales     |
+-------------+----------------+   +-------------+-----------------+
|  id         |  bigint(20)    |   |  id         |  bigint(20)     |
|  RFC        |  varchar(15)   |   |  IdEmpresa  |  bigint(20)     |
|  Nombre     |  varchar(100)  |   |  Nombre     |  varchar(100)   |
|  Logo_100   |  bigint(20)    |   +-------------+-----------------+
+-------------+----------------+

+-----------------------------+
|          syStorage          | 
+-----------+-----------------+
|  id       |  bigint(20)     |
|  FileB64  |  text           |
+-----------+-----------------+
mysql

1 Answers

  1. Best Answer

    The only thing that you really lack is that you do a GROUP BYfor all the columns that do not come from the table dbEmpresas_Sucursales. With that, now you can use the function COUNTcorrectly and without erroneous results.

    SELECT S.FileB64 AS ImgB64, E.*, COUNT(ES.id) AS Sucursales
FROM dbempresas E
LEFT JOIN systorage S ON S.id = E.Logo_100 
LEFT JOIN dbempresas_sucursales ES ON ES.IdEmpresa = E.id
GROUP BY E.id, E.RFC, E.Nombre, E.Logo_100, S.FileB64
ORDER BY E.Nombre;
    • 2