Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004

    Unanswered: Table Referencing

    Hello everyone,
    I am a novice to SQL and I am generating a query from scratch.
    My situation is:

    I have one table [CONFIGURATION], that holds information about a product configuration. Another table holds the formulas for that configuration. example record

    Configuration ID | MODEL | Formula1 | Formula2 | Formula3|
    1 | NAF | 1 | 4 | 3

    The Formula table is as follows

    Formula ID | Formula | Description
    1 | UH - BH - 3.75 | "tells what the formula is"

    So in the configuration table the Formula1 field would reference Formula ID = 1 and our formula for that situation would be UH - BH - 3.75

    I am storing only unique formulas so that when the formulas change I can make the change in place instead of hunting through thousands of them making the changes.

    I can get one formula just fine but when I try to get formula2 I get either the same data in each formula field or nothing at all.

    I am not sure what methods to use. Right now I am trying Inner Join, but if there is another method made for this kind of query please let me know so that I can research and learn to this correctly.

    Last edited by kwilli; 03-23-04 at 17:12.

  2. #2
    Join Date
    Aug 2003
    Massachusetts, USA
    First you should have an association table between configuration and formulas as there can be many formulas to a configuration. Rather than having multiple columns in configuration as you have stated.

    But to pull the information you want you must join to formulas to configuration 3 times using LEFT OUTER JOIN.

    SELECT something
    FROM configuration a
    JOIN formula f1 ON a.formula1 = f1.formula_id
    LEFT OUTER JOIN formula f2 ON a.formula2 = f1.formula_id
    LEFT OUTER JOIN formula f3 ON a.formula3 = f1.formula_id

Posting Permissions

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