[thelist] dynamic table sql question

Brian Cummiskey Brian at hondaswap.com
Mon Apr 25 14:26:00 CDT 2005


Ed McCarroll wrote:
> SELECT * FROM WhateverTable W
> INNER JOIN jobs J ON J.job_name = W.job_name
> WHERE phonenumber IN ( <<numbers here>> )

That's not going to work...  I would need to be able to cycle through 
WhateverTable for the entire list of tables in the Jobs table.
I would have to go through all 300+ job tables and change WhaterTable to 
reflect that name.

Thanks for the idea though...


Joshua Olsen Wrote:
 > It may be possible to do this in a stored procedure depending on your
 > database.  Which database are you using?

Sorry, i wasn't clear.
I am using M$SQL 2000 (version 7 i think?)
i can use asp/stored proceudre/triggers if need be, but i don't need to 
export the results to anything.  Basically, i just need to get a list of 
the unique ID's from each table in the database where those numbers are 
located.  I'm just trying to run the query in Query Analyzier.

A little info about what i'm doing-  I work for a B2B call center, and 
we have had some requests to remove from our call list.  I need to find 
all instances of these numbers if they exist for other jobs, and set the 
status to 'u' for uncallable in every table.  There's a good chance that 
our lists will overlap for big jobs.


Teke at union.com Wrote:
 > Union should do the trick.

yes, it would-  but that would still require me to manually union all 
300+ tables.  Not something i'm looking forward to doing, and human 
error can exist--  theres a good chance i could miss one by accident.


Jay Blanchard Wrote:
 > I am not sure that you have provided enough information here or I just
 > don't understand, but lets see if we can make order of it. You need to
 > check a large number of tables for a list of phone numbers from one
 > table? What is your database software and are you using PHP or ASP or
 > PERL or anything like that?

No, i need to check if the phone numbers exist in MULTIPLE tables. 
Thus, i want to generate a sub-query that will populate the table names. 
  Luckily, i already have a table that holds this information--  that's 
the Jobs table, which holds the JobID, which is the name of the 300+ 
tables i need to look in.

As stated above, this is MS SQL.  don't really need a web front end.  I 
plan to run an update query once i have all the rowid's for the records 
to set the callable status to 'u' for uncallable.



Thanks for the help so far guys.  Let's see if this added info can get 
us anywhere :)




More information about the thelist mailing list