[thelist] dynamic table sql question

[Ken Schaefer]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: thelist-bounces at lists.evolt.org [mailto:thelist-
: bounces at lists.evolt.org] On Behalf Of Brian Cummiskey
: Subject: Re: [thelist] dynamic table sql question
: 
: Peter Brunone (EasyListBox.com) wrote:
: >    Well, my first reaction would be "why the heck did they design the
: database this way", but since I doubt you have any control over that,
: let's press on.
: 
: It actually makes perfect sense.  Each Job is from a different client,
: upon which we are calling on THEIR lists for them.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Actually, from where I'm sitting and reading this thread, it doesn't make
sense at all.

My suspicion is reinforced by the fact that you're having a little difficulty
coming up with an SQL query that produces the results you want. Generally, a
properly normalised database design gives you queries that are easy to write.

Since you are using MS SQL Server 7, can you give us a bunch of CREATE TABLE
scripts? (or alternatively describe your design in ER terms, since your
current description below doesn't really make much sense).

Cheers
Ken

--
www.adOpenStatic.com/cs/blogs/ken/

 
: Perhaps I need some sample data to get this together a little better.  I
: don't think anyone is really grasping what i'm dealing with.
: 
: 
: So, We have for example 2 clients.
: companyA and companyB each with 2 current jobs.
: 
: each table has a unique ID to THAT table, as well as a jobid field,
: which is the same as the table name (for all purposes here.. its a
: little different, but thats not an issue i can't handel)
: 
: There is also a Master table, called Jobs
: which has the Jobid (which is called companyA_1) and holds other
: useless-to-this-post stuff like description, running dates, etc..
: 
: basically, i need to select all job ids from jobs.
: 
: this will give me
: 
: companyA_1
: companyA_2
: companyB_1
: companyA_2
: 
: 
: and from those tables, i need to generate the:
: 
: select rowID, jobname from <<each of the above table names>>
: where phonenumber in ( <<the number list>> )
: 
: this will hopefully give me
: 
: row	jobname
: 12345	companyA_1
: 22345	companyA_2
: 32345	companyb_1
: 42345	companyB_2
: 
: if there were 4 rows among those tables with the phone numbers i plug in.
: 
: 
: Is this making more sense now?
: 
: which is why i want to dynmically geenerate my table name that i select
: from.
: 
: I thought about doing it in a scripting language... something like