2

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
forpas
  • 160,666
  • 10
  • 38
  • 76
Ajowi
  • 449
  • 3
  • 12
  • 1
    A [mcve] would make things much clearer. What result do you want? What does "fails" mean? – jarlh Dec 27 '21 at 09:07
  • Do you want only the table names that contain the row or full rows from the tables? – forpas Dec 27 '21 at 09:22
  • @jarlh; this is my first question and I think the question is already verbose enough. it fails here means MySQL will complain that there is a syntax error near the lines with consecutive limit-by-clauses. – Ajowi Dec 27 '21 at 09:27
  • @forpas, only an array containing the list of tables; table names only. – Ajowi Dec 27 '21 at 09:29
  • this question has been solved by @forpas as shown below; the first option tested and works fine. thanks to all. – Ajowi Dec 27 '21 at 09:46

1 Answers1

1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • 1
    I have just tested the first option and it works; exactly what I have been searching for for the last 6 hours. thanks a lot. – Ajowi Dec 27 '21 at 09:43