Multiple Tables Search, with Limit Clause and Known Result Source Table Names-CodePudding

A situation arose where I have to heuristically find out a table name from a common id. The tables have the same exact structure so I thought union or union all will work as suggested by some answers here. The problem is, applying a limit clause to the query is not working well. If I apply it on more than one select query, it fails; if on one, it only filters on that table as primary. Even wrapping it as shown below is not producing the desired result - which is: limit the returned result from each of those individual queries to say one row.

Restating the question: How can I know if a list of known tables have a particular value (named-column) or not?

An open option gives good table sources as aliases but multiple limits are prohibited; results could be very many:

SELECT `ID`, `BATCH_NUMBER`, 'TBL1' AS 'table_one' FROM `table_one` WHERE `NAMED_ID` = '123' 
UNION ALL
SELECT `ID`, `BATCH_NUMBER`, 'TBL2' AS 'table_two' FROM `table_two` WHERE `NAMED_ID` = '123' 
UNION ALL
SELECT `ID`, `BATCH_NUMBER`, 'TBL3' AS 'table_three' FROM `table_three` WHERE `NAMED_ID` = '123'

Wrapped option filters only on the first table results are found; we need all tables with the value.

select * from (
SELECT `ID`, `BATCH_NUMBER`, 'TBL1' AS 'table_one' FROM `table_one` WHERE `NAMED_ID` = '123' 
UNION ALL
SELECT `ID`, `BATCH_NUMBER`, 'TBL2' AS 'table_two' FROM `table_two` WHERE `NAMED_ID` = '123' 
UNION ALL
SELECT `ID`, `BATCH_NUMBER`, 'TBL3' AS 'table_three' FROM `table_three` WHERE `NAMED_ID` = '123' 
) as resutl_table  
LIMIT 1

CodePudding user response：

If you want only the table names then each of your queries should return only the 1st column:

SELECT 'TBL1' AS table_name FROM `table_one` WHERE `NAMED_ID` = '123' 
UNION
SELECT 'TBL2' AS table_name FROM `table_two` WHERE `NAMED_ID` = '123' 
UNION
SELECT 'TBL3' AS table_name FROM `table_three` WHERE `NAMED_ID` = '123'

Or, get the results as comma separated string:

SELECT GROUP_CONCAT(table_name) AS table_names
FROM (
  SELECT 'TBL1' AS table_name FROM `table_one` WHERE `NAMED_ID` = '123' 
  UNION
  SELECT 'TBL2' AS table_name FROM `table_two` WHERE `NAMED_ID` = '123' 
  UNION
  SELECT 'TBL3' AS table_name FROM `table_three` WHERE `NAMED_ID` = '123'
) t

UNION will filter out duplicates.