# Equipment table Join Sql query

**URL:** <https://forum.inductiveautomation.com/t/equipment-table-join-sql-query/29619>\
**Category:** Ignition\
**Created:** [November 2, 2019, 7:14pm UTC](https://forum.inductiveautomation.com/t/equipment-table-join-sql-query/29619 "2019-11-02T19:14:20Z")\
**Posts on this page:** 4\
**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:** [November 2, 2019, 7:14pm UTC](https://forum.inductiveautomation.com/t/equipment-table-join-sql-query/29619/1 "2019-11-02T19:14:20Z")

</div>

I have two tables, one is MblEquip and the other is MobileDowntime. The query(below) results only show the Mobile Equipment that had downtime this month it doesn’t show all the equipment. I am wanting to put zero’s for the equipment that doesn’t have any Downtime this month. Any Ideas how to show the other equipment and put zero’s in those rows that are null.

```auto
SELECT a.EquipNbr, case when Sum(a.Downtime) is null then 0 else Sum(a.Downtime)end Downtime FROM MobileDowntime (nolock) a
join MblEquip (nolock) b on a.EquipNbr = b.EquipNbr
Where b.DelFlg = 0 and b.EquipNbr != 'Clean Shop' and a.DateTm Between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
Group By a.EquipNbr Order by a.EquipNbr Asc

```

---

<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:** [November 2, 2019, 8:59pm UTC](https://forum.inductiveautomation.com/t/equipment-table-join-sql-query/29619/2 "2019-11-02T20:59:58Z")

</div>

Use a LEFT join and instead of case use ISNULL(Sum(a.Downtime),0)

---

<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:** [November 2, 2019, 9:41pm UTC](https://forum.inductiveautomation.com/t/equipment-table-join-sql-query/29619/3 "2019-11-02T21:41:52Z")

</div>

It gives me the same results as before

---

<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:** [November 3, 2019, 3:03pm UTC](https://forum.inductiveautomation.com/t/equipment-table-join-sql-query/29619/4 "2019-11-03T15:03:25Z")

</div>

After digging around on the internet, this is the only way I could find that worked the way I needed it to

```auto
Select e.EquipNbr, coalesce(sum(md.Downtime), 0) Downtime
From MblEquip e left join
     MobileDowntime md
     on md.EquipNbr = e.EquipNbr and
        md.DateTm between DATEADD(month, DATEDIFF(month, 0, getDate()), 0) and DATEADD(month, DATEDIFF(month, -1, getDate()), -1)
where e.DelFlg = 0 and e.EquipNbr <> 0000
group by e.EquipNbr 
order by e.EquipNbr Asc;

```