MySQL left join using one line
I have the following tables:
mysql> select * from `empresas`;
+----+-------+-----------+-------+------------+----------------+
| id | tipo | logotipo | nome | grupo | cnpj |
+----+-------+-----------+-------+------------+----------------+
| 8 | Lazer | troll.jpg | Teste | Pespi Cola | 99999313412312 |
+----+-------+-----------+-------+------------+----------------+
mysql> select * from `empresas_contatos`;
+----+---------+------------+--------------+------------+
| id | empresa | rotulo | email | telefone |
+----+---------+------------+--------------+------------+
| 1 | 8 | Principal | x@xxx.co.co | 5112121212 |
| 2 | 8 | Financeiro | fin@y.net | 5012121212 |
+----+---------+------------+--------------+------------+
And I want to use both left joins, for example:
mysql> select `e`.`nome`, `e`.`grupo`, `c`.* from `empresas` `e`
-> left join `empresas_contatos` `c` on
-> `c` . `empresa` = `e` . `id`;
+-------+------------+------+---------+------------+-------------+------------+
| nome | grupo | id | empresa | rotulo | email | telefone |
+-------+------------+------+---------+------------+-------------+------------+
| Teste | Pespi Cola | 1 | 8 | Principal | x@xxx.co.co | 5112121212 |
| Teste | Pespi Cola | 2 | 8 | Financeiro | fin@y.net | 5012121212 |
+-------+------------+------+---------+------------+-------------+------------+
The problem is that in this way the query is iterating over fields empresas
like grupo
, nome
(the real table is bigger than an example!).
I would like to know how can I get all data in one line. The result should be something like this:
+-------+------------+------+---------+------------+-------------+------------+-------------+------------+
| nome | grupo | id | empresa | rotulo | email1 | telefone1 | email2 | telefone2 |
+-------+------------+------+---------+------------+-------------+------------+-------------+------------+
| Teste | Pespi Cola | 1 | 8 | Principal | x@xxx.co.co | 5112121212 | fin@y.net | 5012121212 |
+-------+------------+------+---------+------------+-------------+------------+-------------+------------+
And if there is a third line empresas_contatos
, the query will return email3
, telefone3
...
I could use GROUP_CONCAT (), but I am curious to find a solution for this.
Thanks in advance!
source to share
Basically what you are trying to do PIVOT
. The problem here is that we need the row id before PIVOT
. This is where I entered the row number grouped by the empresa column.
So, something like this should work, but you need to know the maximum number of potential columns. But still, this should get you started:
select g.empresa,
MAX(CASE WHEN rownum = 1 THEN g.email END) Email1,
MAX(CASE WHEN rownum = 2 THEN g.email END) Email2,
MAX(CASE WHEN rownum = 1 THEN g.telefone END) Phone1,
MAX(CASE WHEN rownum = 2 THEN g.telefone END) Phone2
from (
select id,empresa,email,telefone,
@running:=if(@previous=empresa,@running,0) + 1 as rownum,
@previous:=empresa
from empresas_contatos t
JOIN (SELECT @running:= 0) r
JOIN (SELECT @previous:= 0) s
) g
GROUP BY g.empresa
And here is the SQL Fiddle .
BTW - this also looks like your rotulo column will need the same logic as the duplicates.
Here is a more functional working example according to your needs - I removed empresas_contatos.id, but if you want that, just add another MAX CASE:
select e.nome, e.grupo, g.empresa,
MAX(CASE WHEN rownum = 1 THEN g.rotulo END) Rotulo1,
MAX(CASE WHEN rownum = 2 THEN g.rotulo END) Rotulo2,
MAX(CASE WHEN rownum = 1 THEN g.email END) Email1,
MAX(CASE WHEN rownum = 2 THEN g.email END) Email2,
MAX(CASE WHEN rownum = 1 THEN g.telefone END) Phone1,
MAX(CASE WHEN rownum = 2 THEN g.telefone END) Phone2
from (
select id,empresa,email,telefone,rotulo,
@running:=if(@previous=empresa,@running,0) + 1 as rownum,
@previous:=empresa
from empresas_contatos t
JOIN (SELECT @running:= 0) r
JOIN (SELECT @previous:= 0) s
) g JOIN empresas e on g.empresa = e.id
GROUP BY e.nome, e.grupo, g.empresa
And also Fiddle .
source to share