[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