Cross Join, use Joiner node with zero join columns

KNIME Analytics Platform
1

I want to create a Cross Join, or Cross Product -- all possible combinations of two sets of values.

For example, if the Joiner node had the following one-column tables as inputs:

Person
======
Al
Bob
Ian

Year
====
2010
2011

Then the output table would be:

Person | Year
Al     | 2010
Al     | 2011
Bob    | 2010
Bob    | 2011
Ian    | 2010
Ian    | 2011

In SQL, this would be accomplished with the following query:

SELECT a.Person, b.Year FROM Table1 a, Table2 b;

This would work with the KNIME Joiner node, except that the Joiner node currently does not allow having zero "Joining Columns".  This seems like an unnecessary restriction.  Joining two tables without a join condition is a perfectly valid operation to want to perform.

Is there some other KNIME node that will support this operation?

-- Randy Kerber

Kavaii Analytics

2

This is quite a common request, it’s called a Cartesian join or Cartesian product. There is more information half way down this old post
http://tech.knime.org/node/20912

3

Let knime know which records should be joined

Person | isToJoin
======
Al        | 1
Bob     | 1
Ian      | 1

Year    | isToJoin
====
2010   | 1
2011   | 1

(optional: filter out all records you want to use in join)

then, make a join between Person_Table and Year_Table on Person_Table.isToJoin = Year_Table.isToJoin

====================================================

Alternative:

create a temparory SQLite DB named like TempDB.sqlite

save data Person and Year into TempDB.sqlite as table person_blablaproject and year_blablaproject.

SQL out what you want as your SQL script:

SELECT person_blablaproject.Person, year_blablaproject.Year FROM person_blablaproject, year_blablaproject

 

Good Luck!

4

You can use this as a workaround.

 

Add to both tables a column containing some constant value (e.g. using the Java Snippet)

Join the two tables on this value.

Filter the value again.

 

Best, Iris