View with two or more unbound tables, with different columns and that need to be merged

Question

View with two or more unbound tables, with different columns and that need to be merged

Navigation

#1 by (1 votes)

0

I have two tables, "PRIMEIRATABELA" with the fields name, surname, and age, another table "SEGUNDATABELA" with the fields name, last_name, and age both tables has no link but need to join in a view being for example PRIMEIRATABELA.name and SEGUNDATABELA.name would have to be in the same column in the view as VIEWTABELA.nomefinal equally with the other fields. no view with full outer join would partially solve my problem.

sql join view

asked by anonymous 20.09.2018 / 14:25

1 answer

View most current record sql server 2008 How to filter Combobox data using the Laravel Framework?

score 1 · Accepted Answer

How to do

You can do this using the UNION function, thus joining the results of the 2 tables, and using subquery :

SELECT * FROM (
  (SELECT nome as nomefinal, sobrenome, idade FROM primeiratabela)
UNION ALL
  (SELECT name as nomefinal, last_name as sobrenome, age as idade FROM segundatabela)
) tabX
ORDER BY nomefinal

Complementing

In UNION , you will have to have the same fields in querys , even though it is NULL .

Example: in a table I have the fieldX and I do not have the fieldY. Already in tableB, I do not have the X field and I have the Y field.

I can merge into different columns:

SELECT * FROM (
  (SELECT campoX, NULL FROM tabelaA)
UNION ALL
  (SELECT NULL, campoY FROM tabelaB)
) tab_aux

Or in the same column, as I did in your case, using alias :

SELECT * FROM (
  (SELECT campoX as campoU FROM tabelaA)
UNION ALL
  (SELECT campoY as campoU FROM tabelaB)
) tab_aux

More about the function UNION .