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.

Thanks
Keith

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