Join Table Key to Multiple Table Names

Hi there. I haven't been able to figure out how to join a table on column on multiple table names. Here's the situation:

I have a table "tblJob" with a key of jobID. Now for every jobID, the program creates a new table that keeps track of the stock before the jobId was processed and after it was processed to give accurate stock levels and show the difference in stock levels. So, a jobID of 355 would be related to the table: "tblPreStock_335" and "tblPostStock_335". These 2 tables have all the materials in stock and the quantity. Therefore they show how much material was used. I need to figure out the difference in the material in the stock before and after the processing.

That means that I have to get a stockID, get the associated pre and post tables, and then display the difference of ALL the materials in the pre and post tables.

Could someone help me get started on the right path? Even a link to similiar problem that I haven't found would be nice.

ThxWouldn't it be a lot less trouble to have one table for stock, one table for jobs, and one table to show job-stock-usage? That way you could have a practical infinity of jobs and stocks with only three tables.

This is a fundamental database design process called normalization. It is the key to maintaining your sanity as your projects grow!

-PatP|||I totally agree...I don't like this design whatsoever but its all I have to work with because I didn't design it and it's the way things are being done right now.|||I believe that particular schema is taken directly from Chapter 6, page 142 of the ever popular and best-selling book, "WORLD'S WORST DATABASE DESIGNS".

You are going to have to use dynamic SQL to solve this. Essentially, you will construct your SQL statement as a string concatenating the value of jobID in as the table name, and then execute the string.

Developers like that ought to be shot.|||thx for the reply blindman. I was leaning that way but I'm fairly new to using ms sql. I'm currently reading up on dynamic sql and seeing how it works. I need to generate a view out of this somehow.

Thx again|||I'd join the quest for shooting such developers...And Google returns NOTHING on the search for the best-seller...Did you buy the last copy? Maybe tkat11's developer can come up with the second edition...by popular demand ;)|||-----
lol|||I'd join the quest for shooting such developers...And Google returns NOTHING on the search for the best-seller...Did you buy the last copy? Maybe tkat11's developer can come up with the second edition...by popular demand ;)I don't know that it has ever been officially published, it is more of a "work in progress" kind of thing. Every time they think they're ready to publish, somebody runs in yelling "You've got to see this one" and they go right back to editing!

-PatP|||73% of the database designs in that book, WORLD'S WORST DATABASE DESIGNS, were written by php programmers, who design tables like that all the time

18% of them were written by people who've spent too much time at dbdebumph.com and have drunk the koolaid -- not a null in sight!!

9% were written by oracle developers who thought that storing a whole nested table inside a field was a neat idea and supports their concept of object-oriented encapsulation|||Man, you NAILED it with #3!!! I'll buy you a round (12 pack or a bottle of your choice) whenever you're in town!!! Though I see so much of #1 that it's not even funny any more...I wish images from Unreal Tornament were real sometimes...|||thank you, thank you, i'll be here all week, try the veal and don't forget to tip your waitress

:cool:|||no sooner do i say it, and another example of a design in the first category pops up: this thread (http://forums.devshed.com/t199186/s.html)

happens all the time|||This is SO ironic...I just declined an offer from a real estate marketing company which excercised a similar design "strategy" ... They even asked me at the interview how I would resolve this situation... Of course my answer was to FIRE the designer of the current database first ;)