# How to modify query with joins

**URL:** <https://forum.inductiveautomation.com/t/how-to-modify-query-with-joins/38517>\
**Category:** Ignition\
**Created:** [September 9, 2020, 12:44pm UTC](https://forum.inductiveautomation.com/t/how-to-modify-query-with-joins/38517 "2020-09-09T12:44:02Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [September 9, 2020, 12:44pm UTC](https://forum.inductiveautomation.com/t/how-to-modify-query-with-joins/38517/1 "2020-09-09T12:44:02Z")

</div>

The query below gives me the 3 category’s, the sum of hours for each category and the percentage. I have created new tables for a better table structure. I am trying to add joins to this current query for the new tables.

This is the original query

```auto
Select Category,
    Hours, 
    100.0*Hours/(SUM(Hours) OVER (Order by Category ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))Percentage
FROM (
    Select Category, SUM(MaintTech1Hours + MaintTech2Hours + MaintTech3Hours + MaintTech4Hours + MaintTech5Hours) Hours
    From WorkOrder
    Where DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
    Group by Category
) subq

```

This is the query I am trying to get figured out. This has the new table structure.

```auto
Select c.Category,
    Hours, 
    100.0*Hours/(SUM(Hours) OVER (Order by c.Category ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))Percentage
FROM (
    Select c.Category, SUM(b.Hrs) Hours
    From EquipWorkOrder a
    	Join EquipWorkOrderHrs b
    		On b.EquipWorkOrderID = a.EquipWorkOrderID
    	Join Category c
    		On c.CategoryID = a.CategoryID
    	Join Equipment d
    		On d.EquipmentID = a.EquipmentID
    Where a.DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
    Group by c.Category
) subq

```

When I run the new query I get a error which states " The multi-part identifier c.Category could not be bound"

---

<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:** [September 9, 2020, 1:13pm UTC](https://forum.inductiveautomation.com/t/how-to-modify-query-with-joins/38517/2 "2020-09-09T13:13:30Z")

</div>

Does your `Category` table have a `Category` column?

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [September 9, 2020, 1:17pm UTC](https://forum.inductiveautomation.com/t/how-to-modify-query-with-joins/38517/3 "2020-09-09T13:17:29Z")

</div>

yes it is also named Category

---

<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:** [September 9, 2020, 1:20pm UTC](https://forum.inductiveautomation.com/t/how-to-modify-query-with-joins/38517/4 "2020-09-09T13:20:14Z")

</div>

> [@jshaffer09](#):
>
> EquipWorkOrderHrs

Try fixing your join on `EquipWorkOrderHrs` first. That might be throwing off the query optimizer.

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [September 9, 2020, 1:22pm UTC](https://forum.inductiveautomation.com/t/how-to-modify-query-with-joins/38517/5 "2020-09-09T13:22:51Z")

</div>

I fixed the join but I still get the same error

---

<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:** [September 9, 2020, 1:25pm UTC](https://forum.inductiveautomation.com/t/how-to-modify-query-with-joins/38517/6 "2020-09-09T13:25:42Z")

</div>

In the outer query, it should be `subq.Category`.

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [September 9, 2020, 1:53pm UTC](https://forum.inductiveautomation.com/t/how-to-modify-query-with-joins/38517/7 "2020-09-09T13:53:02Z")

</div>

I tried this but It gives me a syntax for " .". I am assuming because It doesn’t know what table Category is in. But when I add the letter for what table it is in I still get a syntax error

```auto
Select c.Category,
    Hours, 
    100.0*Hours/(SUM(Hours) OVER (Order by c.Category ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))Percentage
FROM (
    Select c.Category, SUM(b.Hrs) Hours
    From EquipWorkOrder a
    	Join EquipWorkOrderHrs b
    		On b.EquipWorkOrderID = a.EquipWorkOrderID
    	Join Category c
    		On c.CategoryID = a.CategoryID
    	Join Equipment d
    		On d.EquipmentID = a.EquipmentID
    Where a.DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
    Group by c.Category
) subq.Category

```

---

<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:** [September 9, 2020, 2:00pm UTC](https://forum.inductiveautomation.com/t/how-to-modify-query-with-joins/38517/8 "2020-09-09T14:00:23Z")

</div>

No, in the top three lines.

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [September 9, 2020, 3:05pm UTC](https://forum.inductiveautomation.com/t/how-to-modify-query-with-joins/38517/9 "2020-09-09T15:05:05Z")

</div>

In the first Select?

---

<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:** [September 9, 2020, 5:08pm UTC](https://forum.inductiveautomation.com/t/how-to-modify-query-with-joins/38517/10 "2020-09-09T17:08:59Z")

</div>

Yes, your aliases ‘a’, ‘b’, ‘c’, and ‘d’ are only valid _inside_ the subquery. The output columns of the subquery are then associated with alias you give the subquery itself.

---

<div class="post-metadata">

**Author:** ![jshaffer09](https://avatars.discourse-cdn.com/v4/letter/j/df788c/32.png) [@jshaffer09](https://forum.inductiveautomation.com/u/jshaffer09)\
**Post date:** [September 9, 2020, 5:39pm UTC](https://forum.inductiveautomation.com/t/how-to-modify-query-with-joins/38517/11 "2020-09-09T17:39:23Z")

</div>

I see! That worked

```auto
Select subq.Category,
    Hours, 
    100.0*Hours/(SUM(Hours) OVER (Order by subq.Category ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING))Percentage
FROM (
    Select c.Category, SUM(b.Hrs) Hours
    From EquipWorkOrder a
    	Join EquipWorkOrderHrs b
    		On b.EquipWorkOrderID = a.EquipWorkOrderID
    	Join Category c
    		On c.CategoryID = a.CategoryID
    	Join Equipment d
    		On d.EquipmentID = a.EquipmentID
    Where a.DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
    Group by c.Category
) subq

```