Using the UNION clause is the best way, see an example:
Let’s say there are two conceptual tables: TB_CLIENTES and TB_VENDAS.
TB_CLIENTES(
ID,
NOME,
ENDERECO_RESIDENCIA,
CIDADE_RESIDENCIA
)
TB_VENDAS(
ID,
CLIENTE_ID,
VALOR,
PRODUTO,
ENDERECO_ENTREGA,
CIDADE_ENTREGA
)
With the following records:
TB_CLIENTES
ID NOME ENDERECO_RESIDENCIA CIDADE_RESIDENCIA
1 "José" "R. 13 de Maio" "Monte A. do Sul"
2 "Maria" "R. 23 de Maio" "Serra Negra"
3 "João" "R. 7 de Setembro" "Amparo"
TB_VENDAS
ID CLIENTE_ID VALOR PRODUTO ENDERECO_ENTREGA CIDADE_ENTREGA
1 3 36.0 "Xícara" "R. 9 de Abril" "Pedreira"
2 2 45.0 "Baú" "R. 23 de Mario" "Serra Negra"
3 1 12.0 "Licor" "R. João XXIII" "Amparo"
4 3 55.0 "Panela" "R. Maria III" "Serra"
There is a need to select all customers and/or purchases that the city contains "Serra" on its behalf.
SELECT "CLIENTE" as TIPO, ID, CIDADE_RESIDENCIA as CIDADE, NOME as DISC
FROM TB_CLIENTES
WHERE CIDADE LIKE '%' + @NOME_CIDADE+ '%'
UNION
SELECT "VENDA" as TIPO, ID, CIDADE_ENTREGA as CIDADE, PRODUTO as DISC
FROM TB_VENDAS
WHERE CIDADE LIKE '%' + @NOME_CIDADE+ '%'
As a result we would have:
TIPO ID CIDADE DISC
"CLIENTE" 2 "Serra Negra" "Maria"
"VENDA" 2 "Serra Negra" "Baú"
"VENDA" 4 "Serra" "Panela"
Note that it is necessary to assemble selects so that data types and column names become common among them, only then will you succeed.
And if the types are not the same or the numerical accuracies are different you can use the commands for cast, which each SGDB provides.
Column names must be the same if you have an A table a column named string1 and another B string2 use an alias in table B string2 as string1
– renedet
good, what if one table has more columns than another? the problem is this now, gives an error saying that should have the same number columns
– Gerson
Mount SELECT so that you have the same number of columns. Let’s assume that the Column email exists only in the Second table, and not in the first, then In the first table is SELECT Name, Address, NULL AS email
– Tony