Home / Questions / 97247
Accepted
Dev 200
Asked: 2020-08-25 14:38:03 +0800 CST 2020-08-25 14:38:03 +0800 CST

How to group a record that is repeated, showing all the different records that are associated with it?

  • 772

In MySQL I have the following three tables:

|Personas         |
-------------------
|dni_persona int  | 1
|nombre_persona   | Juan


|Empresas         |
-------------------
|dni_empresa      | 12345    54321
|nombre_empresa   | cocaloca pipse cola


|Personas_Empresas |
--------------------
|dni_persona_a     | 1     1
|dni_empresa_a     | 12345 54321

If I wanted to obtain all the people and together with them the companies in which they can work, I would do this:

SELECT *
FROM personas_empresas as pe

   LEFT JOIN personas as p ON pe.dni_persona_a = p.dni_persona
   LEFT JOIN empresas as e ON pe.dni_empresa_a = e.dni_empresa

Returning the following:

|dni_persona_a|dni_empresa_a|dni_persona|nombre_persona|dni_empresa|nombre_sa|
|1            |12345        |1          |Juan          |12345      |cocaloca |
|1            |54321        |1          |Juan          |54321      |pipseloca|

How can I modify the query to obtain only once Juan but in another column all the companies to which he is related?

To get the following:

|nombre_persona|dni_empresa|nombre_empresa|
|Juan          |12345      |cocaloca      |
|              |54321      |pipseloca     |

Note:

1) Try using DISTINCTbut it returns the same as repeated records.

2) Use groupby nombre_personaand only return one record example juan cocaloca

sql

1 Answers

  1. Best Answer

    The relation of the tables, you should make it from Personas > Personas_Empresa > Empresas , in this way your query would be as follows:

    SELECT
    P.nombre_persona, E.dni_empresa, E.nombre_empresa
FROM Personas P
    INNER JOIN Personas_Empresas PE 
        ON P.dni_persona = PE.dni_persona
    INNER JOIN Empresas E
        ON PE.dni_empresa = E.dni_empresa

    The result would be the following:

    +----------------+-------------+----------------+
| nombre_persona | dni_empresa | nombre_empresa |
+----------------+-------------+----------------+
| Juan           | 12345       | cocaloca       |
+----------------+-------------+----------------+
| Juan           | 54321       | pipse cola     |
+----------------+-------------+----------------+

    Here you can see the demostración y sus resultados.

    Note: SQL has many flavors and between one and another the syntax can change, so you will need to add the tag of the one you currently use. Some are SQL Server, MySQL Oracle, among others. The exposed example works for SQL Server, but I think it can also work for other database engines.

    • 2