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

Asked

Viewed 6,102 times

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 an sql command to generate a single table by placing columns with different names on one side of the other, for example:

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

Can someone please help me?

mysql sql

  • What have you tried? Post a snippet of SQL that you were trying to use

  • I have tried to select from each table and then use Union to join, only it does not show the different columns.

5 answers

5


Just make the Join between the 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)

3

I neither user nor INNER JOIN nor WHERE, I’d wear a face LEFT JOIN (same first answer from @Flaviano Silva), because, if the subsequent tables do not have the relationship data I bring the value of tabela1 and work the other values with 0 or any of your choice.

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

3

All other answers assume that in the 3 tables each person always has the same ID. This is true in the example posted, but I’m not so sure it is so in the actual database. So I also suggest a LEFT JOIN, but by 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);

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 the 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

0

Whereas he put "Mysql" as a tag for his question and spoke on "generate a single table by placing the columns with different names one next to the other, "you can use the natural joins that match the columns of the same name to you, doing what you asked for:

select * 
from Tabela1
natural left join Tabela2
natural left join Tabela3

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.