Question on using Joiner Node with non unique IDs

Good morning / afternoon / evening

Relatively new KNIME user here, trying to wrap my head around the Joiner node

Say I have Table A with one entry per Country, and then a metric ton of data fields about the Country ( Country being a unique ID / no duplicate entries )

Then I Have Table B with one entry per City, and various data fields about the City ( including one for Country ) ( a Unique ID here would be a string of City_Country )

I want to combine these datasets so all of the country-related info is added to Table B - the expected results would have just as many entries as Table B, only a lot more columns

I seem to be unable to get the Joiner node to do this? Entries get multiplied ( say I have two cities in one country, performing an Inner join by Country results in four records instead of two )

I can get around this by doing a long succession of Cell Replacer nodes instead of a Join, but would anyone have a more practical solution?

Many thanks & a great day

Hi @ZWiart.

¿Have you considered split the ID of table B (City_Country) so that the primary keys of both tables match? (Country = Country in joiner node)

Best

@ZWiart to me this sounds like a job for a left join. City table as the leading one (first) table and then a left join with the country table. Should result in the two cities having the exact same entries for their country and two rows.

As always it might be a thing about planning ahead and defining what to do with your data.

Do a Google search on’“SQL Join”. This will explain the concepts behind the joiner node and, whilst the examples will be in SQL, the principles of the join types are the same. It will be better to do the Google search than for the forum to try and explain.

The text above the Venn diagram in the joiner node tells you which join you are getting. Which will make sense once you grasp the basic concept.

1 Like

NVM figured it out
Was baffled by Inner / Left Outer attempts were giving me more results than expected
Turned out I need to do some cleanup in Table A, my “unique ID” is not unique
Have a great day

1 Like

@ZWiart great you figured it out. If you want to further explore the question of duplicates and KNIME I would like to point you to this thread:

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.