SQL Join, Union, Merge

Hi there,

I'm searching for a smart way to fill a view with values from a table
but I cannot find the correct JOIN or UNION :-(

I know the exact outcome but I cannot figure how to write this efficiently.
---
I wrote a Table for some machine-settings:
CREATE TABLE "Settings" ("ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , "PosSollSt3" INTEGER NOT NULL , "PosSollSt3Vk" INTEGER NOT NULL , "PosKurzSt3" INTEGER NOT NULL , "PosKurzSt3Vk" INTEGER NOT NULL)

Depending on the article proportions I want the calculate the positions where the machine needs to go.
These Positions can be calculated using some properties of the article.

My Idea was to add a Table where I can insert the corresponing formulas.

CREATE TABLE "Formel" ("ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE, "Text" TEXT NOT NULL);
INSERT INTO "Formel" VALUES(1,'(@1+@1)*0.99');
INSERT INTO "Formel" VALUES(2,'(@1+@2)*0.99');
INSERT INTO "Formel" VALUES(3,'(@1+@3)*0.99');
INSERT INTO "Formel" VALUES(4,'(@1+@4)*0.99');
INSERT INTO "Formel" VALUES(5,'(@1+@5)*0.99');
...

since I only have three types of articles, I inserted the links to the formulas into the table:

INSERT INTO "Settings" VALUES(1, 1, 2, 3, 4);
INSERT INTO "Settings" VALUES(2, 5, 6, 7, 8);
INSERT INTO "Settings" VALUES(3, 9,10,11,12);

so beginning at Index 4, the INTs are used for the calculated positions of real articles.
I can use C# to read line by line and value by value and replace the numbers with the texts of he formulas.
Outcome:

"PosSollSt3" , "PosSollSt3Vk"  , "PosKurzSt3"  , "PosKurzSt3Vk"
'(@1+@1)*0.99', '(@1+@2)*0.99', '(@1+@3)*0.99', '(@1+@4)*0.99'
'(@1+@5)*0.99', '(@1+@6)*0.99', '(@1+@7)*0.99', '(@1+@8)*0.99'
'(@1+@9)*0.99', '(@1+@10)*0.99', '(@1+@11)*0.99', '(@1+@12)*0.99'

---
What select would you use to create a view?

select "Formel.Text",  "Formel.Text",  "Formel.Text",  "Formel.Text",  from settings
"JOIN each Number"
ON settings.field = Formel.ID
---
I tried all kinds of methods but nothing leads to a working result :-(

Hope someone can help
Thanks

Collin

Hello,
I suppose you have found your solution by that time.
There are at least 2 ways to do that.
One is using PIVOT, but I will suggest a simpler way - multiple JOIN.

In Settings you have in one record 4 different references to Formulas - right?
Then you need to JOIN the Formula for each key, so 4 times, only with different alias:


select FPosSollSt3.Text,  FPosSollSt3Vk.Text,  FPosKurzSt3.Text,  FPosKurzSt3Vk.Text
from Settings S   JOIN Formel as FPosSollSt3 ON S.PosSollSt3 = FPosSollSt3.ID
  JOIN Formel as FPosSollSt3Vk ON S.PosSollSt3Vk = FPosSollSt3Vk.ID
  JOIN Formel as FPosKurzSt3 ON S.PosKurzSt3 = FPosKurzSt3.ID
  JOIN Formel as FPosKurzSt3Vk ON S.PosKurzSt3Vk = FPosKurzSt3Vk.ID

MP