How to create a view in mysql by taking data from 3 tables and repeating the different columns in the result?

4

I have the tables:

+----------Tabela-1--------+
id | nome   |  cnpj   | qtd1
1  | carlos | 0563233 |   4 
+--------------------------+

+----------Tabela-2--------+
id | nome   |  cnpj   | qtd2
1  | carlos | 0563233 |   6 
+--------------------------+

+----------Tabela-3--------+
id | nome   |  cnpj   | qtd3
1  | carlos | 0563233 |   8 
+--------------------------+

I would like to make a sql command to generate a single table by putting the different named columns side by side, for example:

+------------------------------------------------------------------+
 id  | nome   |    cnpj    | qtd1  | qtd2  |  qtd3
  1  | carlos | 0563233    |   4   |  6    |    8
+------------------------------------------------------------------+

Can anyone help me please?

    
asked by anonymous 18.06.2014 / 14:20

5 answers

5

Just join between tables by the field they have in common

SELECT
  T1.id,
  T1.nome,
  T1.cnpj,
  T1.qtd1,
  T2.qtd2,
  T3.qtd3
FROM
  tabela1 T1
  INNER JOIN tabela2 T2 on (T1.id = T2.id)
  INNER JOIN tabela3 T3 on (T1.id = T3.id)
    
18.06.2014 / 14:27
3

You can also use LEFT JOIN

SELECT t1.id, t1.nome, t1.cnpj, t1.qtd1, t2.qtd2, t3.qtd3 FROM tabela1 t1 LEFT JOIN tabela2 t2 on (t1.id = t2.id) LEFT JOIN tabela3 t3 on (t1.id = t3.id)

or with WHERE to make the condition between tables

SELECT t1.id, t1.nome, t1.cnpj, t1.qtd1, t2.qtd2, t3.qtd3 FROM tabela1 t1, tabela2 t2, tabela3 t3 WHERE t1.id = t2.id and t1.id = t3.id
    
18.06.2014 / 14:43
3

I do not have a user or INNER JOIN nor WHERE , I would use a LEFT JOIN (same first @Flaviano Silva), because if the subsequent tables do not have the data of the relation I bring the value of tabela1 and work the other values with 0 or any of its preference. >

SELECT
  T1.id,
  T1.nome,
  T1.cnpj,
  ifnull(T1.qtd1, 0) AS qtd1,
  ifnull(T2.qtd2, 0) AS qtd2,
  ifnull(T3.qtd3, 0) AS qtd3
FROM
  tabela1 T1
  LEFT JOIN tabela2 T2 on (T1.id = T2.id)
  LEFT JOIN tabela3 T3 on (T1.id = T3.id);

Example: SQL Fiddle

    
19.06.2014 / 17:42
3

All other answers assume that in the 3 tables each person always has the same ID. This is true in the posted example, but I'm not so sure it's like this in the real database. Therefore, I also suggest a LEFT JOIN , but for the CNPJ field instead of the ID.

Adapting the Harry Potter query:

SELECT
  T1.id,
  T1.nome,
  T1.cnpj,
  ifnull(T1.qtd1, 0) AS qtd1,
  ifnull(T2.qtd2, 0) AS qtd2,
  ifnull(T3.qtd3, 0) AS qtd3
FROM
  tabela1 T1
  LEFT JOIN tabela2 T2 on (T1.cnpj = T2.cnpj)
  LEFT JOIN tabela3 T3 on (T1.cnpj = T3.cnpj);
    
20.06.2014 / 17:42
0

Considering that you have placed "MySQL" as your question tag and talked about "generating a single table by putting columns with different names next to each other", you can use the natural joins that match the columns of the same name for you, doing what you asked for:

select * 
from Tabela1
natural left join Tabela2
natural left join Tabela3
    
11.07.2014 / 06:59