I'm making a form for a search, and when I look for something, I want it to return various values from several tables that exist.
As such, I would like to know the easiest way to do a query, which is not too heavy and can read all tables with several different fields.
One way is to use the UNION clause and merge the search results:
SELECT 'TABELA 1' AS origem
t1.campo1,
t1.campo2
FROM tabela1 t1
WHERE t1.campo1 like '%texto%'
OR t1.campo2 like '%texto%'
UNION
SELECT 'TABELA 2' AS origem
t2.campo1,
t2.campo2
FROM tabela2 t2
WHERE t2.campo1 like '%texto%'
OR t2.campo2 like '%texto%'
...
UNION
SELECT 'TABELA 10' AS origem
t10.campo1,
t10.campo2
FROM tabela3 t10
WHERE t10.campo1 like '%texto%'
OR t10.campo2 like '%texto%'