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

Asked

Viewed 962 times

0

I have two tables, "PRIMEIRATABLE" with the fields name, surname, and age, another table "SEGUNDATABLE" with the fields name, last_name, and acts the two tables have no link but need to join in a view being that for example FIRST table.name and SECOND table.name would have to stay in the same column in the view as VIEWTABELA.final name equally with the other fields. no view with full Outer Join would partially solve my problem.

sql join view

1 answer

1


How to do

You can do using the function UNION, thus uniting the results of the 2 tables, and using as 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 function UNION, you will have to have the same fields in querys, even if it is NULL.

Example: in a tableA I have the fieldX and I don’t have the fieldCampoy. Already in tableB, I don’t have the fieldX and I have the fieldCampoy.

I can join in 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.

  • That’s exactly what I needed, thank you @Rbz !

Browser other questions tagged sql join view

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