General use of UNION with condition:
SELECT campo1, campo2 FROM tabela1 WHERE ...
UNION
SELECT campo1, campo2 FROM tabela2 WHERE ...
UNION
SELECT campo1, campo2 FROM tabela3 WHERE ...
UNION
SELECT campo1, campo2 FROM tabela4 WHERE ...
Or:
SELECT * FROM (
SELECT campo1, campo2 FROM tabela1
UNION
SELECT campo1, campo2 FROM tabela2
UNION
SELECT campo1, campo2 FROM tabela3
UNION
SELECT campo1, campo2 FROM tabela4
) todos
WHERE condicao
Applying to your case and differentiating tables:
Usually in SQL you do not need to return only fields, you can use expressions involving more than one field, or simply use fields in any of the columns. And that’s what we’re gonna do here:
SELECT 'web_tv' AS tabela, campo1, campo2
^^^^^^^^^^^^^^^^^^
This select will always return a name field tabela
with the content webtv
, to find out where the information came from:
SELECT 'web_tv' AS tabela, campo1, campo2 FROM web_tv WHERE ...condicoes...
UNION
SELECT 'agenda' AS tabela, campo1, campo2 FROM agenda WHERE ...condicoes...
UNION
SELECT 'blog' AS tabela, campo1, campo2 FROM blog WHERE ...condicoes...
UNION
SELECT 'osorio' AS tabela, campo1, campo2 FROM osorio WHERE ...condicoes...
I recommend using only the fields actually needed for the search, in place of the *
, whenever possible. In some situations *
is more practical, but usually specifying the columns is almost always safer.
If you really want to use the *
, can do this:
SELECT 'web_tv' AS tabela, * FROM web_tv WHERE ...condicoes...
UNION
SELECT 'agenda' AS tabela, * FROM agenda WHERE ...condicoes...
...
But it will only give you a headache when you restructure any of them, plus you’re probably returning data that you won’t use.
The important thing is to understand that we created a literal column, with the names of the original table so you know which result came from where.
If you want to simplify, you can use a numeric value:
SELECT 1 AS tabela, * FROM web_tv WHERE ...condicoes...
Simplification tip
This here:
WHERE titulo LIKE '%".$busca."%' OR texto LIKE '%".$busca."%'
It can be simplified in this way:
WHERE concat_ws( ' ', titulo, texto ) LIKE '%".$busca."%'
In the case of two fields it doesn’t change much, but if you have more fields, you can make your life easier.
Alternative syntax
First of all, important to say that when you use this way, probably the UNION
will generate a result set temporary and apply the WHERE
then what is not very performatic. But as in your case it comes to LIKE
with %
of both sides, ends up giving in the same, because anyway will be read the records one by one (in all cases).
SELECT * FROM (
SELECT 'web_tv' AS tabela, campo1, campo2 FROM web_tv
UNION
SELECT 'agenda' AS tabela, campo1, campo2 FROM agenda
UNION
SELECT 'blog' AS tabela, campo1, campo2 FROM blog
UNION
SELECT 'osorio' AS tabela, campo1, campo2 FROM osorio
) todos
WHERE todos.campo1 LIKE '%".$busca."%' OR todos.campo2 ....
ORDER BY todos.campo1
put your sql to try to help you
– tkmtts
I updated the question by adding the query code.
– Vítor André
To use the
union
, the number of columns and their names have to be exactly equal, which if it is the case, it would not make sense to have the records in different tables, you have already tried to make the 4 separate queries bringing everything inarray
and joining afterwards?– Kenny Rafael
They do not have equal tables. The only thing that repeats are ID, TITLE and TEXT. The rest is different in each one. I’ll try to join the results, but I thought there was a way to run the query directly in mysql.
– Vítor André
@Kennyrafael and Vitor, the names do not need to be equal at all, only the amount and types of the ones that are effectively used in select. You can mix names, but to use outside of UNION you will need to use positional syntax (numbers indicating the column instead of the names)
– Bacco
In other words,
SELECT nome, cpf FROM pessoa UNION SELECT razao_social, cnpj FROM empresa
works ifnome
andrazao_social
are of the same type, andcpf
andcnpj
also..– Bacco
Thank you @Bacco
– Kenny Rafael
Thank you @Bacco and Kenny Rafael. I swapped * for the fields I really needed (id, title and text) and fixed the OR that wasn’t working either. Almost all problems have been solved, but I still can’t figure out which table each row is brought... Anyway, thanks.
– Vítor André