[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