[thelist] dynamic table sql question

Keith Gaughan keith at digital-crew.com
Mon Apr 25 14:46:14 CDT 2005


Brian Cummiskey wrote:

> i'm trying to write a simple query to check each and every table for a 
> certain group of phone numbers from another table which lists all the 
> jobs and its properties.
> 
> this is what i want to do...
> 
> 
> select * from (select distinct job_name from jobs)
> where phonenumber IN ( <<numbers here>> )
> 
> basically, i want to cycle through each job name from the Jobs table, 
> and within the same database, there is a table with that Job name.
> 
> I.e. Jobs holds: Job1, Job2
> and I have the following tables:
> Jobs
> Job1
> Job2
> in my main listing under the DB.
> 
> I know this syntax fails..  but is there a way to do such a thing?

Nope, not directly. In fact, I can't fathom why you have so many tables
in the first place for doing something like that.

> I really don't want to go through and do this manually for 300 different 
> job tables to see if those phone numbers are in it.

What you'll need to do is manually do a union on all the tables, or just
use one jobs table.

If you're architecturally stuck with the multitude of tables and this is
an ad-hoc query, you could build the query something like this:

     sql = "SELECT * FROM ("
     for i in xrange(job_names.length):
         sql += "SELECT * FROM " . job_names[i]
         if i < job_names.length:
             sql += " UNION "
     sql += "WHERE phonenumber IN (?)"

     stmt = conn.prepare(sql)
     stmt.set(1, phone_numbers)
     rs = stmt.execute()

Excuse the bastard Java/Python pseudocode. :-)

K.



More information about the thelist mailing list