CODEWITHSUNDEEP
sqlselect
Haggai Magen
Haggai Magen

Reputation: 103

SQL select from each table from result set of tables

I have a database named db with tables with the same schema such as:

table_1, table_2, ...table_n

Suppose each table has the columns 'key' and 'value' and I would like to select all the rows in all the n tables where key = 'x'

for example, for the following tables for key = '1':

table_1 = {'1' => 'aaa', '2' => 'xyz'}

table_2 = {'1' => 'bbb, '3' => 'zzz'}

table_3 = {'1' => 'ccc, '3' => 'xxx'}

I would expect the result set to be {'1' => 'aaa', '1' => 'bbb', '1' => 'ccc'}

I looked it up and found out that I can get all the tables by selecting them from the INFORMATION_SCHEMA like this:

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('value')
    AND TABLE_SCHEMA='db';

But now I need to find a way to iterate through the result set in such a manner (pseudo code):

foreach (table in tablesResultSet)
{ results += SELECT * FROM table WHERE key = 'x' }

I apologize for the crude pseudo code but I find that it is the best way to describe the issue. Thank you for your help.

Upvotes: 0

Views: 390

Answers (1)

Andrew Lazarus
Andrew Lazarus

Reputation: 19340

Generally, multiple tables with the same schema go to one large logical table that is partitioned by date, location, or some other discriminator. This can be much more efficient in terms of archiving, querying, etc.

But if you want to do this, you should work with an RDBMS that supports table inheritance or some equivalent concept. You don't want to play with the internal structures of the DB, and then have to build a query on the fly dependent on the results.

Postgresql 10 allows you to set up a partitioning scheme where almost everything is done for you; earlier versions (and 10 retains) inheritance relationships that let you do this with a little work. I suspect other vendors have similar capabilities, and if you really need a partitioned set-up (which can't be determined from your dummy example), I would choose an RDBMS on the basis of this capability.

Upvotes: -1

Related Questions