How to group a repeating record, showing all the different records associated with it?

Question

How to group a repeating record, showing all the different records associated with it?

Navigation

#1 by (2 votes)

3

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 get all the people and next to them the companies where 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 get only once but in another column all the companies to which it is related?

To get the following:

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

Note:

1) Try using DISTINCT but return the same repeated records.

2) Use groupby nombre_persona and just return an example log juan cocaloca

sql

asked by Victor Alvarado 25.08.2017 в 00:38

source

1 answer

Remove null or empty values from a JSON Object in Google AppScript Work in the same branch and same file with git

score 2 · Accepted Answer

The relationship of the tables, you should make it from Personas > Personas_Empresa > Empresas , this way your query would be the following Form:

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 demo and its results .

Note: SQL has a lot of flavors and between them you can change the syntax, so you will need to add the tag you are currently using. Some are SQL Server, MySQL Oracle, among others. The example above works for SQL Server, but I think that it can work in the same way in other database engines.