[00:00] In this lesson, I'll show you how to set up a nested query in your report to use the results of one query to drive another. Let's first take a look at two example tables that I have in my database. First I have a table with records of equipment. It has ID name and description columns. Then there's a table that holds information about downtime events. This has a column for the ID of the downtime event, the column with the ID of the equipment that went down, and then columns for the cause of the downtime and the minutes it was down. I want to include data from both of these tables in my report. We can see that they're related through the ID column on equipment_table and equipment ID on downtime_table. What I'd like to do is run a query that returns data about each piece of equipment and then I can use that to tell me about any downtime for each piece of equipment. Luckily a nested query will help me do this. I'll start by creating a SQL query data source, and I'm going to change the name of the data key to "equipment". I have a query already copied that I'll paste here and we can quickly go over.

[01:07] Basically, this query is returning each of the columns from the equipment_table and assigning them aliases. ID becomes equipment ID number, name becomes equipment name and description becomes equipment description. This will be my parent query. In order to add a child query. I'll go over to the nested query section and click the plus icon. I only need one child for this example. But if you need more in your nested query system, you can continue to click the plus icon to add children to your child or you can also add peers as well. I'll change the data key of my child query to equip downtime and grab my other query. This query is returning the cause and minutes down columns from the downtime table and assigning them aliases. It's also using a "where" clause to filter the results on the equipment_ID column. I want to filter the results of this query based on the results of the parent query in order to do this. I'll need a parameter which uses curly brace syntax. And then my parameter name is going to be the name of the column from the first table that relates to this table. If you remember that column is called "id", but I gave it in Alias of "equipment ID Number" in my query.

[02:17] So I can use equipment ID number as my parameter if I wasn't using an alias, I would just use ID here. It's a good idea to use aliases here because if I didn't and I have a typo in the parameter field the child would search every column of every parent query for a match and if it didn't find one there it would look in the parameters for a match as well. If it ends up finding something that's similarly named you might get yourself into a situation where you're looking at the wrong data and it could be difficult to identify why. So aliases can be helpful here. So I have my nested query set up and I want to test this. I'll head over to the preview Tab and we can look at the results. I won't go over how I set this table up. If you'd like, you can reference a table groups page of the user manual. All you need to know is that the results of the parent query are gray and the results of the child query are red.

[03:04] The way that this works is the parent queries executed in returns a certain number of rows. Then the child queries are run for every row returned by the parent. It's important to note that a new query is run for each row returned by a parent query. If you have additional nested children queries that run for each row returned by the first set of children. The number of queries can exponentially increase. It's important to be aware of runtime implications. System performance could drop as a result of a sufficiently complex query system and you may have to wait a while for your results. Now you may be thinking that this example could also be achieved with the SQL join and you'd be right. However, nested queries are beneficial as they can be much easier to write and maintain compared to complex join statements and they allow for greater control. The biggest benefit is also that nested queries have much less restriction than a SQL join would nested queries can be set up to relate data across different schemas databases and even other data sources like the tag historian which make them extremely powerful tool.