# How to JOIN two tables on serperate databases?

**URL:** <https://forum.inductiveautomation.com/t/how-to-join-two-tables-on-serperate-databases/85115>\
**Category:** Ignition\
**Created:** [February 1, 2024, 7:11pm UTC](https://forum.inductiveautomation.com/t/how-to-join-two-tables-on-serperate-databases/85115 "2024-02-01T19:11:12Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![NP209](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/np209/32/80704_2.png) [@NP209](https://forum.inductiveautomation.com/u/NP209)\
**Post date:** [February 1, 2024, 7:11pm UTC](https://forum.inductiveautomation.com/t/how-to-join-two-tables-on-serperate-databases/85115/1 "2024-02-01T19:11:12Z")

</div>

I need help with an SQL Query between two tables in separate databases.

I want to return all rows from **PacakageRecord** table on our **Bizerba** database where both the **ArticleNumber** from our **PackageRecord** matches the **ItemNumber** from **OrderStartStop** table on our **Ignition\_Historian\_MSSQL** database.

I noticed the query builder inside ignition doesn't allow joining between databases, or doesn't look like it does. Is there a way to specify where to look for all the information?

here is what I have currently

```auto
SELECT PackageRecord.ActualNetWeightValue, PackageRecord.ArticleNumber
FROM PackageRecord
JOIN OrderStartStop ON PackageRecord.ArticleNumber = OrderStartStop.ItemNumber;

```

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [February 1, 2024, 7:14pm UTC](https://forum.inductiveautomation.com/t/how-to-join-two-tables-on-serperate-databases/85115/2 "2024-02-01T19:14:12Z")

</div>

I suspect that you're going to have to script multiple queries or named queries and script the join.

The part of your question, " both the **ArticleNumber**" seems to be incomplete as there isn't a second requirement specified.

---

<div class="post-metadata">

**Author:** ![YF129701](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/yf129701/32/60498_2.png) [@YF129701](https://forum.inductiveautomation.com/u/YF129701)\
**Post date:** [February 1, 2024, 7:29pm UTC](https://forum.inductiveautomation.com/t/how-to-join-two-tables-on-serperate-databases/85115/3 "2024-02-01T19:29:05Z")

</div>

If the databases are on the same server, you should be able to join like so (semi-psuedocode below):

```sql
SELECT
    *
FROM Bizerba.<schema>.PacakageRecord pr
INNER JOIN Ignition_Historian_MSSQL.<schema>.OrderStartStop oss
    ON oss.ItemNumber = pr.ArticleNumber
WHERE ...

```

If they're not on the same server, you can do this is by [creating a linked server](https://www.mytecbits.com/microsoft/sql-server/joining-tables-different-servers) and then write your query similar to above. We've done this before with SQL Server and it works exactly as you would expect. I also don't ever use the Database Query Browser tool for anything non-trivial, just use an RDBMS if available and it'll give you suggestions, table names, etc.

You should be able to just write a named query once you get the syntax figured out. For most of the Named Queries I create, there is a corresponding stored procedure in the database that I then just `EXEC` from the Named Query, but this depends on your requirements and setup (this is more of a description, not a prescription).

---

<div class="post-metadata">

**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:** [February 1, 2024, 8:07pm UTC](https://forum.inductiveautomation.com/t/how-to-join-two-tables-on-serperate-databases/85115/4 "2024-02-01T20:07:02Z")

</div>

You might find the various `join()` functions in my Integration Toolkit helpful.

> [@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…