How to find a word at once in three different tables in a db?

Question

SQL

Good afternoon.
There are 3 tables in the database: one, two, three. Each has a display_name field and just a name. When receiving a word, it is necessary to find in one query in which table and in which column it is located. The word is exact, i.e. without LIKE. Can this be done with one request? I would not want to produce 3 different ones ... The tables have no relationship with each other (i.e. join will not work).

Answer 1

And through UNION tried?

Answer 2

You can dynamically substitute the table name in the search, but for this you still have to write a function or procedure or script to iterate over several tables.

Answer 3

With this initial problem statement, it will always be three separate queries. Another thing is that they can be combined through the sql UNION construct .
Here is an abstract example:

select
  display_name
  , 'Table one' as TableName
from
  one
where
  [display_name] = 'Искомое слово'
union all
select
  display_name
  , 'Table two' as TableName
from
  two
where
  [display_name] = 'Искомое слово'
union all
select
  display_name
  , 'Table three' as TableName
from
  three
where
  [display_name] = 'Искомое слово'

The output will be a table of two columns with the search word and the name of the table where it was found.

Answer 4

SELECT * FROM
( SELECT 'one' AS TableName, 'display' AS FieldName, display_name AS Search FROM One AS O1
UNION
SELECT 'one', 'name', name  FROM One AS O2
UNION
SELECT 'two', 'display', display_name  FROM Two AS T1
UNION
SELECT 'two', 'name', name  FROM Two AS T2
UNION
SELECT 'three', 'display', display_name  FROM Three AS T3
UNION
SELECT 'three', 'name', name  FROM Three AS T4) AS TableSearch
WHERE Search = 'Word'

Answer 5

join won't work

how the main WHERE and not table names will go
to Join, Mysql does Cartesian product of the listed tables anyway, and only where allows a part in each set (table) to be discarded
, but the problem statement itself is bad