Suppose there are several tables that have similar fields (not necessarily the same name: title, name ...). It is necessary to make a sample of the type LIKE% ??%, so that in the end it is understood in which particular table the value is found.

That is, such a query displays all the rows found, but does not identify the table:

SELECT title, id AS id_1 FROM tbl_1 WHERE title LIKE %$txt% UNION SELECT title, id AS id_2 FROM tbl_2 WHERE title LIKE %$txt%

That is, even if the first table is empty and the second is found, the result assigned to tbl_1 will be displayed:

 [title] => xxx [id_2] => 123

For clarity:
http://sqlfiddle.com/#!2/126255/2/0

And I would like to see the result like this:

 [title] => xxx [id_1] => null [id_2] => 123 [title] => xxxzzz [id_1] => null [id_2] => 456 [title] => wwwxxx [id_1] => 1212 [id_2] => null ...
  • hashcode.ru/questions/311718 - zb '
  • In this example, the tables are linked by idCountry / CountryId, while in my example the tables are not related at all. - Pioner
  • There the main magic word is pivot, and your tables are linked by title or something else, I don’t quite understand, but if there is no connection, then there is no solution. - zb '

1 answer 1

In general, yes. There is no connection, but the decision came up. Maybe not perfect, but working:

 SELECT title,id,('t_1') AS tbl_name FROM tbl_1 WHERE title LIKE '%$query%' UNION SELECT title,id,('t_2') FROM tbl_2 WHERE title LIKE '%$query%'

Now in the tbl_name field you can see the name of the table in which a match is found. http://sqlfiddle.com/#!2/126255/24/0