Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2003
    Posts
    73

    Unanswered: 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.

    Thx

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wouldn'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

  3. #3
    Join Date
    Oct 2003
    Posts
    73
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2003
    Posts
    73
    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

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Oct 2003
    Posts
    73

    Talking

    --------------
    lol

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thank you, thank you, i'll be here all week, try the veal and don't forget to tip your waitress

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no sooner do i say it, and another example of a design in the first category pops up: this thread

    happens all the time
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •