0

Is it mandatory to know the table name from which you want to fetch some data based on some criteria? For example, here I have a database with 20 tables containing the same columns, only different row data.

And I want to fetch some data, from outside, based on some criteria, without being able to know the table name. Is there some sort of query available, something like:

SELECT * FROM SOME TABLE WHERE ID = [randomnumber]
MWR
  • 304
  • 1
  • 2
  • 12
  • You need to specify the table name, however you could create a table containing the table name and matching criteria as columns. you could then use this table to get the required data. – Aaron Pereira Jul 01 '19 at 09:55
  • From what you've told me that would seem highly inefficient. Is there really no other way than to create temporary tables? – MWR Jul 01 '19 at 09:57
  • 1
    wouldn't it be easier if all the tables were all in one? so u would just have to query from one table only – Ryan Tan Jul 01 '19 at 10:02
  • Is there a reason/ some specific condition based on which you are segregating your data? – Aaron Pereira Jul 01 '19 at 10:05
  • Ryan Tan, actually that's what I should have done... sigh. It's a project I'm doing that has to "seem" complex. I could've easily merged them in one table. Ok anyway. The reason is basically what I said above, Aaron. – MWR Jul 01 '19 at 10:08
  • hmm, the only way I could think of is first query the list of tables in the schema. `SELECT TABLE_NAME AS Tables FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'myschema'` and then looop through all the tables with your condition and union all – Ryan Tan Jul 01 '19 at 10:14
  • Looks like you want something like [Dynamic SQL in MySQL Stored Procedure](https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure) – Abra Jul 01 '19 at 10:23
  • What about creating a view that unions all of the tables, then having the application query the view? Wouldn't matter what table the necessary rows were in..... – Eric Brandt Jul 01 '19 at 10:35
  • Eric, that sounds like it would work. Could you maybe create an example? Thanks! – MWR Jul 01 '19 at 13:02

0 Answers0