Dofactory.com
Dofactory.com
 Back to list
Views:   4.9K
Replies:  1
Archived

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

Collin Sale, Jul 07, 2016
Reply 1
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
Maciej Puchalski, Oct 06, 2016
Stay Inspired!
Join other developers and designers who have already signed up for our mailing list.
Terms     Privacy     Cookies       Do Not Sell       Licensing      
Made with    in Austin, Texas.  - vsn 44.0.0
© Data & Object Factory, LLC.