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
Advertisement
Answer
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.