# LEFT JOIN with another database connection

**URL:** <https://forum.inductiveautomation.com/t/left-join-with-another-database-connection/12268>\
**Category:** Ignition\
**Created:** [October 21, 2016, 3:19pm UTC](https://forum.inductiveautomation.com/t/left-join-with-another-database-connection/12268 "2016-10-21T15:19:09Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Curlyandshemp](https://avatars.discourse-cdn.com/v4/letter/c/bcef8e/32.png) [@Curlyandshemp](https://forum.inductiveautomation.com/u/Curlyandshemp)\
**Post date:** [October 21, 2016, 3:19pm UTC](https://forum.inductiveautomation.com/t/left-join-with-another-database-connection/12268/1 "2016-10-21T15:19:09Z")

</div>

I am getting this error:  
[attachment=2]2016-10-21 11\_14\_15-192.168.11.10 - Remote Desktop Connection.png[/attachment]

i am trying to LEFT JOIN the cutomer ID # from a MSSQL database with text descriptions stored in a MySQL database. The MSSQL is the customer’s database and I cannot touch it. Does the JOIN alias support a remote database?

[attachment=1]2016-10-21 11\_12\_28-192.168.11.10 - Remote Desktop Connection.png[/attachment]

[attachment=0]2016-10-21 11\_13\_07-192.168.11.10 - Remote Desktop Connection.png[/attachment]

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [October 21, 2016, 3:34pm UTC](https://forum.inductiveautomation.com/t/left-join-with-another-database-connection/12268/2 "2016-10-21T15:34:18Z")

</div>

You can’t run any query over more than one database connection.

---

<div class="post-metadata">

**Author:** ![Curlyandshemp](https://avatars.discourse-cdn.com/v4/letter/c/bcef8e/32.png) [@Curlyandshemp](https://forum.inductiveautomation.com/u/Curlyandshemp)\
**Post date:** [October 21, 2016, 3:42pm UTC](https://forum.inductiveautomation.com/t/left-join-with-another-database-connection/12268/3 "2016-10-21T15:42:20Z")

</div>

arrrrg, was afraid of that.  
Thanks  
Kevin

---

<div class="post-metadata">

**Author:** ![MMaynard](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mmaynard/32/3366_2.png) [@MMaynard](https://forum.inductiveautomation.com/u/MMaynard)\
**Post date:** [October 21, 2016, 3:47pm UTC](https://forum.inductiveautomation.com/t/left-join-with-another-database-connection/12268/4 "2016-10-21T15:47:55Z")

</div>

Are you allowed to do a Linked Server to the mySQL server?  
If you can set it up as a linked server then you can do joins against it.

---

<div class="post-metadata">

**Author:** ![TedNewman](https://avatars.discourse-cdn.com/v4/letter/t/85e7bf/32.png) [@TedNewman](https://forum.inductiveautomation.com/u/TedNewman)\
**Post date:** [October 22, 2016, 12:31am UTC](https://forum.inductiveautomation.com/t/left-join-with-another-database-connection/12268/5 "2016-10-22T00:31:01Z")

</div>

The easiest way might be to bind a custom dataset property to the query for the main table with an extra empty column for the customer name, then add a property-changed event for that custom property. That event would run your customer query and assign the proper customer description to the customer column of the other dataset and assign that new dataset to the actual Data property.

I (very quickly) threw this code together as an idea of how you could do this, so it’s not guaranteed bug free:

```auto
if event.propertyName == "MyCustomProperty":
    #Run customer query
    customerDataset = system.db.runQuery("SELECT customerid, description FROM QualityCheeseMySQL.customers")
    #Convert to a dict for faster customer lookups in the format {id:"description"}
    customerDict = {}
    for row in system.dataset.toPyDataSet(customerDataset): #using a pyDataSet to make use of python's iterators
        customerDict[row["customerid"]] = row["description"]
    
    #Get the data from the custom property where the main query is bound
    queryData = system.dataset.toPyDataSet(event.source.MyCustomProperty)
    #Use the GL1A field to get the corresponding description from the dictionary
    for row in queryData:
        row["customer"] = (customerDict[row["GL1A"]] if row["GL1A"] in customerDict else None)
    
    #Convert back to a dataset and assign it
    event.source.data = system.dataset.toDataSet(queryData)
```

And your bound query would need to include GL1A for the lookup, so it would look something like:

```auto
SELECT
    DEVICE,
    GL07 as 'Product',
    GT33 as 'Lot #',
    GL1A,
    LEFT(PD00,2) as 'Unit',
    CAST(SUBSTRING(PD00,5,1) AS DECIMAL) +
    CAST(SUBSTRING(PD00,7,3) AS DECIMAL)/1000 as 'Weight'

FROM
    PackageData

WHERE
    {Root Container.Report Viewer.WhereClause}

ORDER BY GT33 desc
```

Again, this code is just an example and has not been tested yet!

Hope that helps.

---

<div class="post-metadata">

**Author:** ![Curlyandshemp](https://avatars.discourse-cdn.com/v4/letter/c/bcef8e/32.png) [@Curlyandshemp](https://forum.inductiveautomation.com/u/Curlyandshemp)\
**Post date:** [October 24, 2016, 7:47pm UTC](https://forum.inductiveautomation.com/t/left-join-with-another-database-connection/12268/6 "2016-10-24T19:47:48Z")

</div>

[quote=“TedNewman”]The easiest way might be to bind a custom dataset property to the query for the main table with an extra empty column for the customer name, then add a property-changed event for that custom property. That event would run your customer query and assign the proper customer description to the customer column of the other dataset and assign that new dataset to the actual Data property.

I (very quickly) threw this code together as an idea of how you could do this, so it’s not guaranteed bug free:

```auto
if event.propertyName == "MyCustomProperty":
    #Run customer query
    customerDataset = system.db.runQuery("SELECT customerid, description FROM QualityCheeseMySQL.customers")
    #Convert to a dict for faster customer lookups in the format {id:"description"}
    customerDict = {}
    for row in system.dataset.toPyDataSet(customerDataset): #using a pyDataSet to make use of python's iterators
        customerDict[row["customerid"]] = row["description"]
    
    #Get the data from the custom property where the main query is bound
    queryData = system.dataset.toPyDataSet(event.source.MyCustomProperty)
    #Use the GL1A field to get the corresponding description from the dictionary
    for row in queryData:
        row["customer"] = (customerDict[row["GL1A"]] if row["GL1A"] in customerDict else None)
    
    #Convert back to a dataset and assign it
    event.source.data = system.dataset.toDataSet(queryData)
```

And your bound query would need to include GL1A for the lookup, so it would look something like:

```auto
SELECT
    DEVICE,
    GL07 as 'Product',
    GT33 as 'Lot #',
    GL1A,
    LEFT(PD00,2) as 'Unit',
    CAST(SUBSTRING(PD00,5,1) AS DECIMAL) +
    CAST(SUBSTRING(PD00,7,3) AS DECIMAL)/1000 as 'Weight'

FROM
    PackageData

WHERE
    {Root Container.Report Viewer.WhereClause}

ORDER BY GT33 desc
```

Again, this code is just an example and has not been tested yet!

Hope that helps.[/quote]

Thanks, was thinking something along that solution, but ended up getting to create the required table in MSSQL database