# Join 2 tables from different databases **URL:** \ **Category:** Ignition\ **Tags:** database, scripting, perspective\ **Created:** [November 18, 2024, 1:50pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734 "2024-11-18T13:50:00Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![I\_Zayah\_Griffin](https://avatars.discourse-cdn.com/v4/letter/i/a4c791/32.png) [@I\_Zayah\_Griffin](https://forum.inductiveautomation.com/u/I_Zayah_Griffin)\ **Post date:** [November 18, 2024, 1:50pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/1 "2024-11-18T13:50:00Z")
Hello All, I am looking for some assistance I am having trouble trying to merge a table into another, I have searched multiple different forums and scripts I just don't know where I am going wrong if at all I would like to be as simple as putting the exact database table into a named query if not I am open to other suggestions ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 18, 2024, 1:55pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/2 "2024-11-18T13:55:21Z")
You cannot do this in a single binding. JDBC doesn't have any mechanism to join across different connections. (But some databases can make their own, "foreign", connection, and do this for you.) If you cannot make one of the databases connect to the other on your behalf, you will need two bindings, and join them yourself. A script to do the join is a common approach, but I recommend my Integration Toolkit's expression functions for this task. > [@Automation Professionals' Integration Toolkit Module](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934/42): > > I had some dead time today. Party! Anyways, several new functions: alias() wraps a dataset with a string prefix for each column name nanoTime() just calls java's System.nanoTime() and returns the timestamp. crossJoin() does what you'd think with two datasets. innerJoin() takes two datasets, plus pairs of iterator expressions to match up. leftJoin() as for innerJoin(), but includes a null right row if a row on the left has no match on the right. For Ignition v8.1+: v2.0.6.232… ---
**Author:** ![I\_Zayah\_Griffin](https://avatars.discourse-cdn.com/v4/letter/i/a4c791/32.png) [@I\_Zayah\_Griffin](https://forum.inductiveautomation.com/u/I_Zayah_Griffin)\ **Post date:** [November 18, 2024, 1:59pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/3 "2024-11-18T13:59:34Z")
Can you please provide an example of how the crossjoin() or innerjoin() function works ---
**Author:** ![I\_Zayah\_Griffin](https://avatars.discourse-cdn.com/v4/letter/i/a4c791/32.png) [@I\_Zayah\_Griffin](https://forum.inductiveautomation.com/u/I_Zayah_Griffin)\ **Post date:** [November 18, 2024, 2:42pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/4 "2024-11-18T14:42:58Z")
This is more or less what I have ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/c/4cbb04b391290640a735cf374b0ed17482b683c0.png) All I need is to join on the reason\_id I really just need the other database to get the classification is there an easy way to do that ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [November 18, 2024, 3:01pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/5 "2024-11-18T15:01:26Z")
> [@pturmel](#): > > you will need two bindings From the documentation... innerJoin(datasetLeft, datasetRight, leftKeyExpr, rightKeyExpr [, ...]) ```python innerJoin( pathToDSBinding1, pathToDSBinding2, "Reason_id", "Reason_id" ) ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 18, 2024, 3:06pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/6 "2024-11-18T15:06:03Z")
What Daniel said. The binding that gets the classifications needs to get all of them that **can** be in the downtime table. ---
**Author:** ![I\_Zayah\_Griffin](https://avatars.discourse-cdn.com/v4/letter/i/a4c791/32.png) [@I\_Zayah\_Griffin](https://forum.inductiveautomation.com/u/I_Zayah_Griffin)\ **Post date:** [November 18, 2024, 3:07pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/7 "2024-11-18T15:07:12Z")
And this is going to be in a script correct ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [November 18, 2024, 3:11pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/8 "2024-11-18T15:11:07Z")
No, an expression binding, not a script. A script would be the alternative method, but less performant. Note: you must install Phil's Integration Toolkit module for the `innerJoin` expression to be available for use. ---
**Author:** ![I\_Zayah\_Griffin](https://avatars.discourse-cdn.com/v4/letter/i/a4c791/32.png) [@I\_Zayah\_Griffin](https://forum.inductiveautomation.com/u/I_Zayah_Griffin)\ **Post date:** [November 18, 2024, 3:20pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/9 "2024-11-18T15:20:12Z")
where do I go to install Phil's Integration Toolkit module or find the module ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 18, 2024, 3:22pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/10 "2024-11-18T15:22:12Z")
Follow what I linked. Scroll to the bottom to find the latest. Or go through the IA Module Showcase. ---
**Author:** ![I\_Zayah\_Griffin](https://avatars.discourse-cdn.com/v4/letter/i/a4c791/32.png) [@I\_Zayah\_Griffin](https://forum.inductiveautomation.com/u/I_Zayah_Griffin)\ **Post date:** [November 18, 2024, 7:55pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/11 "2024-11-18T19:55:49Z")
I believe I am almost there here is what I have: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/5/659d4ca76a9fe5441d10b839d495b69d10cf7917.png) as you can see there are only 3 rows and columns in my original dataset I need to join them so I have 4 columns in total the classification class is there a for loop I can use to do this or some simple way more or less ( if id = 1 new col val (class) = 1) ---
**Author:** ![I\_Zayah\_Griffin](https://avatars.discourse-cdn.com/v4/letter/i/a4c791/32.png) [@I\_Zayah\_Griffin](https://forum.inductiveautomation.com/u/I_Zayah_Griffin)\ **Post date:** [November 18, 2024, 7:56pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/12 "2024-11-18T19:56:29Z")
The second table holds all the classes that I would need ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 18, 2024, 7:59pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/13 "2024-11-18T19:59:58Z")
It looks like your table of classifications has repeat reason IDs. There's no other way for a left join on that column to blow up the number of rows. ---
**Author:** ![I\_Zayah\_Griffin](https://avatars.discourse-cdn.com/v4/letter/i/a4c791/32.png) [@I\_Zayah\_Griffin](https://forum.inductiveautomation.com/u/I_Zayah_Griffin)\ **Post date:** [November 18, 2024, 8:05pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/14 "2024-11-18T20:05:08Z")
![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/7/d71938af4178d103ac2460d19cf5ad341da8279f.png) I believe what is happening my 3x3 dataset is encompassing a where clause I just need the join to be the same reason\_id and class as you can see it doing 3 times per reason\_id and class ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 18, 2024, 8:10pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/15 "2024-11-18T20:10:06Z")
I see what's happening. You put just the column names as your join conditions. You actually need to extract the values with `it()`, like so: ```python leftJoin( {value}, {[default]New Tag}, it()['Reason_id'], it()['Reason_id'] ) ``` Wrap that inside a `columnRearrange()` to drop any undesired columns. ---
**Author:** ![I\_Zayah\_Griffin](https://avatars.discourse-cdn.com/v4/letter/i/a4c791/32.png) [@I\_Zayah\_Griffin](https://forum.inductiveautomation.com/u/I_Zayah_Griffin)\ **Post date:** [November 18, 2024, 8:27pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/16 "2024-11-18T20:27:21Z")
Thank you so much that worked! the last thing I need help is there an easy way to group by class and add the total\_time. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/8/a/8af68158d3f47346849c42fcd095a47670047712.png) ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 18, 2024, 8:32pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/17 "2024-11-18T20:32:37Z")
Uhm, `groupBy()`, perhaps? (Probably needs to be a separate result, not more rows.) ---
**Author:** ![I\_Zayah\_Griffin](https://avatars.discourse-cdn.com/v4/letter/i/a4c791/32.png) [@I\_Zayah\_Griffin](https://forum.inductiveautomation.com/u/I_Zayah_Griffin)\ **Post date:** [November 18, 2024, 9:46pm UTC](https://forum.inductiveautomation.com/t/join-2-tables-from-different-databases/98734/18 "2024-11-18T21:46:50Z")
The Groupby() didn't work like I hoped any other suggesions