# JOIN WITH SOME TROUBLE **URL:** \ **Category:** KNIME Analytics Platform\ **Created:** [June 24, 2022, 9:13am UTC](https://forum.knime.com/t/join-with-some-trouble/44500 "2022-06-24T09:13:55Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![gcas](https://forum-cdn.knime.com/user_avatar/forum.knime.com/gcas/32/41241_2.png) [@gcas](https://forum.knime.com/u/gcas)\ **Post date:** [June 24, 2022, 9:13am UTC](https://forum.knime.com/t/join-with-some-trouble/44500/1 "2022-06-24T09:13:55Z")
HI ALL, i have some problem with 2 tables and a JOIN. in table A (Left), I have different lines like this: (all are uniques) |A1|AAAA| |A2|BBBB| |A3|CCCC| In a table B (right) i have lines like this: (lines are not uniques, sometimes are duplicated) |AAAA|123| |AAAA|123| |AAAA|123| |BBBB|0| |BBBB|0| |CCCC|9999| |CCCC|9999| |CCCC|9999| |CCCC|9999| When i join together the lines of table A increase for how much rows find the record in a table B. I fix this with “Duplicate row filter” before to join the table B, but i this i should have a different solutino, right? How can i join only with only 1 row of table B? ![immagine](https://forum-cdn.knime.com/uploads/default/original/3X/f/7/f78f366904d24e6648b5628fb5ac6c1bfde4d8f2.png) ---
**Author:** ![lelloba](https://forum-cdn.knime.com/user_avatar/forum.knime.com/lelloba/32/43992_2.png) [@lelloba](https://forum.knime.com/u/lelloba)\ **Post date:** [June 24, 2022, 10:07am UTC](https://forum.knime.com/t/join-with-some-trouble/44500/2 "2022-06-24T10:07:13Z")
Hi gcas, let me try to help. What if you use a group by node on table B before the join, so that you won’t have repetitions in B? With group by you can aggregate column 2B using sum, average, etc. or just selecting the first value. The join should result as you asked. RB ---
**Author:** ![gcas](https://forum-cdn.knime.com/user_avatar/forum.knime.com/gcas/32/41241_2.png) [@gcas](https://forum.knime.com/u/gcas)\ **Post date:** [June 24, 2022, 12:06pm UTC](https://forum.knime.com/t/join-with-some-trouble/44500/3 "2022-06-24T12:06:00Z")
Thanks for you suggest! “Group By” is an alternative solution of “remove duplicate”. I didn’t know this node (thanks!), but i think is not the best solution. In my mind JOIN node should have the option i think is missing: match only 1 time ---
**Author:** ![lelloba](https://forum-cdn.knime.com/user_avatar/forum.knime.com/lelloba/32/43992_2.png) [@lelloba](https://forum.knime.com/u/lelloba)\ **Post date:** [June 24, 2022, 12:12pm UTC](https://forum.knime.com/t/join-with-some-trouble/44500/4 "2022-06-24T12:12:52Z")
If you think it can be an useful feature, you can suggest it in this part of the forum: ![immagine](https://forum-cdn.knime.com/uploads/default/original/3X/9/3/9393abd4d126add57410797ad999c4b060bf28b0.png) I don’t think that it is currently available. RB ---
**Author:** ![takbb](https://forum-cdn.knime.com/user_avatar/forum.knime.com/takbb/32/57077_2.png) [@takbb](https://forum.knime.com/u/takbb)\ **Post date:** [June 24, 2022, 12:58pm UTC](https://forum.knime.com/t/join-with-some-trouble/44500/5 "2022-06-24T12:58:26Z")
Hi @gcas , The joiner node performs the standard “relational” joins and it’s not common to have a _join-on-first_ option but I can understand there may be an occasional need to do this. Whilst there is no direct option in the joiner to do it, you can replicate this behaviour without using the duplication removal by “ranking” the rows within the keys that you wish to join on. ![image](https://forum-cdn.knime.com/uploads/default/original/3X/0/8/08f7e46e48e87be54028c351f2d75038ab302db5.png) For the table that is the “lookup table” (Table A in your example), you just assign every row a “rank” of 1. ![image](https://forum-cdn.knime.com/uploads/default/original/3X/d/5/d58ab133ab54eacad7e490e58a3a6ed7990b5334.png) For the other table, you can assign each row a rank grouped within “key” ![image](https://forum-cdn.knime.com/uploads/default/original/3X/0/8/08f7e46e48e87be54028c351f2d75038ab302db5.png) ![image](https://forum-cdn.knime.com/uploads/default/original/3X/0/b/0b22bc59ab5da7817f87ed405c77861284c20ec9.png) I added an extra column to your Table B containing to demonstrate this. ![image](https://forum-cdn.knime.com/uploads/default/original/3X/f/9/f98d3cb798327073d6807a192e8610fce3f11ced.png) After the ranking node, each row is ranked as follows: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/2/1/210b559b41628907921623c7ea0fc7f411213f57.png) (EDIT) In the joiner, you include the “rank” column in the join. Since your lookup table only has 1 as its ranks, only the first row will match for each key. In the joiner we elect to remove the rank nodes as part of the join. Here I have elected to include the (right) unmatched rows too: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/c/d/cdf92cd0d3b66074d3e07785b2fd38ae89ea4784.png) and so you then get match-on-first ![image](https://forum-cdn.knime.com/uploads/default/original/3X/d/c/dc2100c8c21e9b91f26cbc91b86fbdd730895f9f.png) or if you only include matched rows you get the same output as if you’d removed duplicates: ![image](https://forum-cdn.knime.com/uploads/default/original/3X/5/b/5bd8b3b82d44492c25919d1b27a0eb555c2a6209.png) [Join on first match only.knwf](https://forum.knime.com/uploads/short-url/nJbuj4UosZfchFkaGxlrjpNe7wD.knwf) (13.7 KB) Personally I would generally do duplicate removal as you already suggested. I know it’s still extra steps, but I hope that helps if duplication removal is for some reason otherwise causing problems. ---
**Author:** ![gcas](https://forum-cdn.knime.com/user_avatar/forum.knime.com/gcas/32/41241_2.png) [@gcas](https://forum.knime.com/u/gcas)\ **Post date:** [June 24, 2022, 1:06pm UTC](https://forum.knime.com/t/join-with-some-trouble/44500/6 "2022-06-24T13:06:02Z")
Thanks a lot for your reply and approach! I could use your solution waiting for a _join-on-first (or last)_ option in Join Node. Have a nice day! ---
**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\ **Post date:** [June 24, 2022, 2:41pm UTC](https://forum.knime.com/t/join-with-some-trouble/44500/7 "2022-06-24T14:41:31Z")
@gcas with a lot of databases you can use Window functions like RANK to have more control over which record gets selected at the end. You might have to have a criterion to determine which row would get choosen. I have discussed options here > [@School of duplicates - and how to deal with them](https://forum.knime.com/t/school-of-duplicates-and-how-to-deal-with-them/24164): > > Dealing with duplicates is a constant theme with data scientist. And a lot of things can go wrong. The easiest ways to deal with them is SQL’s GROUP BY or DISTINCT. Just get rid of them and be done. But as this examples might demonstrate this might not always be the best option. Even if your data provider swears your combined IDs are unique especially in Big Data scenarios there might still be lurking some muddy duplicates and you should still be able to deal with them. And you should be able t… An example whith H2 database is here > **[use H2 to produce a Position / Rank number within a group variable (window...](https://hub.knime.com/mlauber71/spaces/Public/forum/kn_forum_31608_h2_group_rank_window_function~W6pGUf-DhT3OQALr/current-state)** > > use H2 to produce a Position / Rank number within a group variable (window functions) use custom JDBC driver - THX tobias.koetter for the hint > [@RANK variable in DB](https://forum.knime.com/t/rank-variable-in-db/30564/7): > > @chanoufi_marwa one way to do it is to use a Hive environment and a RANK function. Other SQL databases should also support RANK like H2 ([Window Functions](https://www.h2database.com/html/functions-window.html#rank)). Hint: the KNIME implementation of H2 currently has the version 1.4.196. Unfortunately with H2 the RANK etc functions are only supported with version 1.4.198 or later … @ScottF might be an interesting case to update that version slight_smile ---
**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\ **Post date:** [July 1, 2022, 2:41pm UTC](https://forum.knime.com/t/join-with-some-trouble/44500/8 "2022-07-01T14:41:58Z")
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.