Icon

kn_​example_​sqlite_​multiple_​joins_​40

SQLite - multiple joins with KNIME

I set up a demo how this could be done in KNIME although you could do that in consecutive steps or directly in a SQL database (as has been mentioned) but it is possible. Here I use SQLite which I like because it is a ‘real’ SQL database but you can just have it within one single file (you could switch that out for any SQL database).

The Join itself is done in the SQL Executor node. Plus: you have it in one step. The downside could be it might get a little bit confusing with all the steps, but you have it in one step (sort of :sunglasses:).

import data from excel(or any other source) bring data into SQL Database Join all three sources together and see if result is OK SQLite - multiple joins with KNIME DROP TABLE IF EXISTS data2;CREATE TABLE data2 ASSELECT *FROM data1 d1 LEFT JOIN dimension1 t1 ON d1.ID1 = t1.ID2 LEFT JOIN dimension2 t2 ON d1.ID1 = t2.ID3 knime://knime.workflow/tdata2.tablecreate db.sqlitedata1 into SQLite DBdimension1 into SQLite DBdimension2 into SQLite DBbring all three tables togetherinto data2read data2data1.xlsxdimension1.xlsxdimension2.xlsxdata2.xlsx Table Writer SQLite Connector DB Writer DB Writer DB Writer DB SQL Executor DB Query Reader Excel Reader (XLS) Excel Reader (XLS) Excel Reader (XLS) Excel SheetAppender (XLS) import data from excel(or any other source) bring data into SQL Database Join all three sources together and see if result is OK SQLite - multiple joins with KNIME DROP TABLE IF EXISTS data2;CREATE TABLE data2 ASSELECT *FROM data1 d1 LEFT JOIN dimension1 t1 ON d1.ID1 = t1.ID2 LEFT JOIN dimension2 t2 ON d1.ID1 = t2.ID3 knime://knime.workflow/tdata2.tablecreate db.sqlitedata1 into SQLite DBdimension1 into SQLite DBdimension2 into SQLite DBbring all three tables togetherinto data2read data2data1.xlsxdimension1.xlsxdimension2.xlsxdata2.xlsxTable Writer SQLite Connector DB Writer DB Writer DB Writer DB SQL Executor DB Query Reader Excel Reader (XLS) Excel Reader (XLS) Excel Reader (XLS) Excel SheetAppender (XLS)

Nodes

Extensions

Links