# JOINing two tables in an INSERT INTO **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [September 9, 2025, 6:24pm UTC](https://forum.inductiveautomation.com/t/joining-two-tables-in-an-insert-into/110498 "2025-09-09T18:24:38Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Chris\_Buehrer](https://avatars.discourse-cdn.com/v4/letter/c/9de053/32.png) [@Chris\_Buehrer](https://forum.inductiveautomation.com/u/Chris_Buehrer)\ **Post date:** [September 9, 2025, 6:24pm UTC](https://forum.inductiveautomation.com/t/joining-two-tables-in-an-insert-into/110498/1 "2025-09-09T18:24:38Z")
I’m working on a downtime reporting function that runs a query when the downtime event is over. It currently logs the time of the downtime event (datetime), the machine it occurred on (int) , the reason code (real), the duration (real) and the crew (char). Script from the tag value changed script looks like this: ```python val1 = stopread.value val2 = 1 val3 = dtread.value val4 = (nowtime.time - val1.time)/1000/60 val5 = crewread.value args = [val1,val2,val3,val4,val5] query = "INSERT INTO DownTime (t_stamp,Machine,ReasonCode,Duration,crew) VALUES (?,?,?,?,?)" system.db.runPrepUpdate(query,args,"Ignition_MSSQL") ``` My end results are working fine - I’m able to create a bar chart in Perspective, but I’m having trouble in the reporting system “transforming” the downtime reason code to the text label it represents, eg 20 = “Broken Press”. I’ve created a second table with a reason code (real) and reason code label (nvarchar(50)). What I’d like to do is when I create the entry into the downtime table, I’d like to include the label text into downtime table. I have added a column in the DownTime table called LabelText (nvarchar(50)). It seems like I want to use some sort of JOIN function, but I’m not sure how to do that with the INSERT INTO VALUES function.. Any pointers? ---
**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:** [September 9, 2025, 6:33pm UTC](https://forum.inductiveautomation.com/t/joining-two-tables-in-an-insert-into/110498/2 "2025-09-09T18:33:40Z")
Don't do this on INSERT. Do the JOIN when retrieving the data. ---
**Author:** ![Chris\_Buehrer](https://avatars.discourse-cdn.com/v4/letter/c/9de053/32.png) [@Chris\_Buehrer](https://forum.inductiveautomation.com/u/Chris_Buehrer)\ **Post date:** [September 9, 2025, 7:44pm UTC](https://forum.inductiveautomation.com/t/joining-two-tables-in-an-insert-into/110498/3 "2025-09-09T19:44:52Z")
When my Data Source for the report looks like this: SELECT Downtime.ReasonCode, SUM(Downtime.Duration) FROM DownTime WHERE Downtime.t\_stamp BETWEEN ? AND ? GROUP BY "ReasonCode" ORDER BY "ReasonCode" ASC I get this: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/6/562940fdeac46fd042092973088f84845479d00e.png) Heading toward the right answer - each ‘category’ of down time lists the duration of downtime associated with it. Now I want to replace the numbers on the X-axis with their corresponding category labels. but I can’t seem to structure my JOIN correctly. My best guess is: SELECT DownTimeLabels.LabelDescription, SUM(DownTime.Duration) FROM DownTime WHERE DownTime.t\_stamp BETWEEN '2025-09-08' AND '2025-09-09' GROUP BY DownTime.ReasonCode ORDER BY DownTime.ReasonCode ASC INNER JOIN DownTimeLabels ON DownTime.ReasonCode=DownTimeLabels.NumericID Where the table DownTimeLabels contains two columns NumericID (which should correspond to the column ReasonCode from the table DownTime) and LabelDescription (which contains the nvarchar(50) reason label) (I replaced the ? Parameters with actual values to use in the Database Query Browser). This gives me an incorrect syntax error near INNER. ---
**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:** [September 9, 2025, 7:55pm UTC](https://forum.inductiveautomation.com/t/joining-two-tables-in-an-insert-into/110498/4 "2025-09-09T19:55:41Z")
> [@Chris\_Buehrer](#): > > Parameters with actual values to use in the Database Query Browser). This gives me an incorrect syntax error near INNER. This clearly tells you that the SQL syntax is incorrect, so you should [Google](https://www.google.com/search?q=mssql+inner+join&sca_esv=fe6362944b97d7fe&source=hp&ei=DobAaPL1Ef6VwbkP7sPHqQ4&iflsig=AOw8s4IAAAAAaMCUHjMzrEYIN3nuSYUiPIyC3pfx0BXa&ved=0ahUKEwjytJ7susyPAxX-SjABHe7hMeUQ4dUDCCI&uact=5&oq=mssql+inner+join&gs_lp=Egdnd3Mtd2l6IhBtc3NxbCBpbm5lciBqb2luMgUQABiABDIFEAAYgAQyBRAAGIAEMgcQABipBhgeMgcQABipBhgeMgcQABipBhgeMgcQABipBhgeMgcQABipBhgeMgcQABipBhgeMgcQABipBhgeSPkVUABYwRRwAHgAkAEBmAHWAaABnRCqAQYzLjEyLjG4AQPIAQD4AQGYAg-gAoUPwgIOEC4YgAQYsQMYgwEYigXCAgsQABiABBixAxiDAcICDhAuGIAEGLEDGNEDGMcBwgILEC4YgAQYsQMYgwHCAgsQLhiABBjRAxjHAcICERAuGIAEGLEDGNEDGIMBGMcBwgILEC4YgAQYxwEYrwHCAg4QABiABBixAxiDARiKBcICCBAuGIAEGLEDwgIIEAAYgAQYsQPCAgoQABiABBixAxgKmAMAkgcEMi4xM6AHt2yyBwQyLjEzuAeFD8IHBTAuOS42yAc1&sclient=gws-wiz) for the syntax needed in your DB flavor. However, I think the inner join always goes after the `FROM` clause. ```sql SELECT DownTimeLabels.LabelDescription, SUM(DownTime.Duration) FROM DownTime INNER JOIN DownTimeLabels ON DownTime.ReasonCode=DownTimeLabels.NumericID WHERE DownTime.t_stamp BETWEEN '2025-09-08' AND '2025-09-09' GROUP BY DownTime.ReasonCode ORDER BY DownTime.ReasonCode ASC ``` ---
**Author:** ![Chris\_Buehrer](https://avatars.discourse-cdn.com/v4/letter/c/9de053/32.png) [@Chris\_Buehrer](https://forum.inductiveautomation.com/u/Chris_Buehrer)\ **Post date:** [September 9, 2025, 8:02pm UTC](https://forum.inductiveautomation.com/t/joining-two-tables-in-an-insert-into/110498/5 "2025-09-09T20:02:17Z")
That was it. JOIN should have been earlier. Also I can’t group by ReasonCode because I didn’t include it in my select. This was the final thing that worked: SELECT DownTimeLabels.LabelDescription, SUM(DownTime.Duration/60) FROM DownTime JOIN DownTimeLabels ON DownTime.ReasonCode=DownTimeLabels.NumericID WHERE DownTime.t\_stamp BETWEEN ? AND ? GROUP BY DownTimeLabels.LabelDescription ORDER BY SUM(DownTime.Duration/60) ASC Thanks for the help ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [September 9, 2025, 8:08pm UTC](https://forum.inductiveautomation.com/t/joining-two-tables-in-an-insert-into/110498/6 "2025-09-09T20:08:44Z")
> [@Chris\_Buehrer](#): > > reason code (real) Is it actually a `REAL`? That's almost certainly not what you want. Just because a reason code happens to be something like `digits.digits`, doesn't mean you should represent it with REAL. If you do, then how do you distinguish between `123.1` and `123.10` and `123.100`? ---
**Author:** ![Chris\_Buehrer](https://avatars.discourse-cdn.com/v4/letter/c/9de053/32.png) [@Chris\_Buehrer](https://forum.inductiveautomation.com/u/Chris_Buehrer)\ **Post date:** [September 9, 2025, 8:29pm UTC](https://forum.inductiveautomation.com/t/joining-two-tables-in-an-insert-into/110498/7 "2025-09-09T20:29:01Z")
Fair point. The idea was that each major category (20, 30, etc.) was a different functional area of the process and then I could add subcategories (20.1, 20.2, 20.3). By making it a real I thought it would be easier in a query to sum up all of the subcategories (WHERE ReasonCode BETWEEN 20 AND 30, for example). I’ll have to keep in mind the 123.1 / 123.10 “Gottcha”. Maybe make the first level of decimal subcategories 20,00001, 20.00002, etc.? ---
**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:** [September 9, 2025, 8:31pm UTC](https://forum.inductiveautomation.com/t/joining-two-tables-in-an-insert-into/110498/8 "2025-09-09T20:31:22Z")
Create the subcategories as a separate field / column. Grouping then becomes very simple. ---
**Author:** ![richardNZ15](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/richardnz15/32/33373_2.png) [@richardNZ15](https://forum.inductiveautomation.com/u/richardNZ15)\ **Post date:** [September 9, 2025, 8:31pm UTC](https://forum.inductiveautomation.com/t/joining-two-tables-in-an-insert-into/110498/9 "2025-09-09T20:31:54Z")
I achieved this with larger INT’s. For example: Not Running = 400 → 499. Subcategories of Not Running like ESTOP, Motor Fault etc.. could then be 401, 402 etc.. And had a table dedicated to linking sub categories and main categories to let you query it how you’d like ---
**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:** [September 9, 2025, 9:09pm UTC](https://forum.inductiveautomation.com/t/joining-two-tables-in-an-insert-into/110498/10 "2025-09-09T21:09:39Z")
No, decimals stored in real (floating point) columns are not perfect decimals and you will not be able to use "equals" comparisons reliably. Don't try to create hierarchies with decimals. ---
**Author:** ![Chris\_Buehrer](https://avatars.discourse-cdn.com/v4/letter/c/9de053/32.png) [@Chris\_Buehrer](https://forum.inductiveautomation.com/u/Chris_Buehrer)\ **Post date:** [September 10, 2025, 3:58pm UTC](https://forum.inductiveautomation.com/t/joining-two-tables-in-an-insert-into/110498/11 "2025-09-10T15:58:20Z")
I actually just bumped into that. My downtime code for 60.1, shows up as 60.09999847….. in my bar chart. I could format but that may be a show-stopper for that approach. I’ve done this different ways in past lives but never found an approach that didn’t have some limitation - such is life, Thanks for all of the suggestions - richardNZ15’s suggestion with INTs might work for me. ---
**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:** [September 10, 2025, 4:05pm UTC](https://forum.inductiveautomation.com/t/joining-two-tables-in-an-insert-into/110498/12 "2025-09-10T16:05:01Z")
I don't use numbers where they aren't meaningful **as numbers**. You should use strings, and use alphanum comparators to sort them. ---
**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:** [September 10, 2025, 4:07pm UTC](https://forum.inductiveautomation.com/t/joining-two-tables-in-an-insert-into/110498/13 "2025-09-10T16:07:18Z")
If you use the two columns for `category` and `subcategory` you can easily retrieve either the top level analysis or the detail analysis.