# Making multiple SQL queries into one

**URL:** <https://forum.inductiveautomation.com/t/making-multiple-sql-queries-into-one/38292>\
**Category:** General Discussion\
**Created:** [September 2, 2020, 11:51am UTC](https://forum.inductiveautomation.com/t/making-multiple-sql-queries-into-one/38292 "2020-09-02T11:51:55Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![ilikebeer](https://avatars.discourse-cdn.com/v4/letter/i/eb8c5e/32.png) [@ilikebeer](https://forum.inductiveautomation.com/u/ilikebeer)\
**Post date:** [September 2, 2020, 11:51am UTC](https://forum.inductiveautomation.com/t/making-multiple-sql-queries-into-one/38292/1 "2020-09-02T11:51:55Z")

</div>

I have multiple SQL Queries that I am trying to combine into one query for a report. They work just fine independently but when I try to combine into one query, I get faults and I do not know how to correct this as I am really green.  
SELECT A.BM01\_Infeed AS ‘Infeed’,  
A.t\_stamp AS ‘Start Time’, B.t\_stamp ‘End Time’, TIMESTAMPDIFF(Second,A.t\_stamp,B.t\_stamp) AS ‘InfeedSeconds’  
FROM didata.BM01\_Activity A  
INNER JOIN didata.BM01\_Activity B ON B.BM01\_Activity\_ndx = (A.BM01\_Activity\_ndx + 1)  
WHERE A.BM01\_Infeed = 1 AND  
A.t\_stamp \>= ‘{Root Container.CalendarStart.formattedDate}’ AND A.t\_stamp \<= ‘{Root Container.CalendarEnd.formattedDate}’  
ORDER BY A.BM01\_Activity\_ndx ASC;

SELECT A.BM01\_Run AS ‘Run’,  
A.t\_stamp AS ‘Start Time’, B.t\_stamp ‘End Time’, TIMESTAMPDIFF(Second,A.t\_stamp,B.t\_stamp) AS ‘RunSeconds’  
FROM didata.BM01\_Activity A  
INNER JOIN didata.BM01\_Activity B ON B.BM01\_Activity\_ndx = (A.BM01\_Activity\_ndx + 1)  
WHERE A.BM01\_Run = 1 AND  
A.t\_stamp \>= ‘{Root Container.CalendarStart.formattedDate}’ AND A.t\_stamp \<= ‘{Root Container.CalendarEnd.formattedDate}’  
ORDER BY A.BM01\_Activity\_ndx ASC;

SELECT A.BM01\_Down AS ‘Down’,  
A.t\_stamp AS ‘Start Time’, B.t\_stamp ‘End Time’, TIMESTAMPDIFF(Second,A.t\_stamp,B.t\_stamp) AS ‘DownSeconds’  
FROM didata.BM01\_Activity A  
INNER JOIN didata.BM01\_Activity B ON B.BM01\_Activity\_ndx = (A.BM01\_Activity\_ndx + 1)  
WHERE A.BM01\_Down = 1 AND  
A.t\_stamp \>= ‘{Root Container.CalendarStart.formattedDate}’ AND A.t\_stamp \<= ‘{Root Container.CalendarEnd.formattedDate}’  
ORDER BY A.BM01\_Activity\_ndx ASC;  
SELECT A.BM01\_Discharge AS ‘Discharge’,  
A.t\_stamp AS ‘Start Time’, B.t\_stamp ‘End Time’, TIMESTAMPDIFF(Second,A.t\_stamp,B.t\_stamp) AS ‘DischargeSeconds’  
FROM didata.BM01\_Activity A  
INNER JOIN didata.BM01\_Activity B ON B.BM01\_Activity\_ndx = (A.BM01\_Activity\_ndx + 1)  
WHERE A.BM01\_Discharge = 1 AND  
A.t\_stamp \>= ‘{Root Container.CalendarStart.formattedDate}’ AND A.t\_stamp \<= ‘{Root Container.CalendarEnd.formattedDate}’  
ORDER BY A.BM01\_Activity\_ndx ASC;

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [September 2, 2020, 11:56am UTC](https://forum.inductiveautomation.com/t/making-multiple-sql-queries-into-one/38292/2 "2020-09-02T11:56:38Z")

</div>

If I am reading this correctly, it looks like all your `joins` and `where` clauses are identical right? I would just put every column you need in the `SELECT` clause, leave the rest alone, and try that. What error if any are you getting when you try to do that?

---

<div class="post-metadata">

**Author:** ![ilikebeer](https://avatars.discourse-cdn.com/v4/letter/i/eb8c5e/32.png) [@ilikebeer](https://forum.inductiveautomation.com/u/ilikebeer)\
**Post date:** [September 2, 2020, 11:59am UTC](https://forum.inductiveautomation.com/t/making-multiple-sql-queries-into-one/38292/3 "2020-09-02T11:59:54Z")

</div>

I made a copy/paste error. I have corrected. Can you look over now and see if you think it is the same problem. I am REALLY new and trying to understand all this. No formal education, just reading and .learning

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [September 2, 2020, 12:07pm UTC](https://forum.inductiveautomation.com/t/making-multiple-sql-queries-into-one/38292/4 "2020-09-02T12:07:19Z")

</div>

Ok I see 4 differnet where clauses now. Thats fine, you CAN have multiple SQL queries to provide your data to the report and it seems like that may be what you want. If you combine the `WHERE` clauses of each queries with `AND`, you are getting the intersection of all of them, only items that would be `A.BM01_Infeed = 1 AND A.BM01_Run = 1 AND A.BM01_Down = 1 AND A.BM01_Discharge = 1`, which is probably not what you need, and semantically it seems like `Run` and `Down` would cancel each other out to make your result set empty.

You could combine it with `OR` but your data would not be nicely organized. I would just make 4 separate SQL queries as your Data Sources if I was you.

If you need refreshing, I would highly recommend going to [https://www.inductiveuniversity.com/](https://www.inductiveuniversity.com/) and going to the Reporting Module courses.

---

<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 2, 2020, 12:12pm UTC](https://forum.inductiveautomation.com/t/making-multiple-sql-queries-into-one/38292/5 "2020-09-02T12:12:42Z")

</div>

It looks like you are trying to make your own version of SQL LEAD or LAG to do uptime/downtime calculations. Consider using those directly (no joining required).

---

<div class="post-metadata">

**Author:** ![ilikebeer](https://avatars.discourse-cdn.com/v4/letter/i/eb8c5e/32.png) [@ilikebeer](https://forum.inductiveautomation.com/u/ilikebeer)\
**Post date:** [September 2, 2020, 12:13pm UTC](https://forum.inductiveautomation.com/t/making-multiple-sql-queries-into-one/38292/6 "2020-09-02T12:13:30Z")

</div>

Thanks for the advice. I’ll give this all a try.  
Refreshing is a kind word for what I need. One of those guys who jumped in the middle of the lake and trying to learn to swim without any prior knowledge.

---

<div class="post-metadata">

**Author:** ![ilikebeer](https://avatars.discourse-cdn.com/v4/letter/i/eb8c5e/32.png) [@ilikebeer](https://forum.inductiveautomation.com/u/ilikebeer)\
**Post date:** [September 2, 2020, 12:16pm UTC](https://forum.inductiveautomation.com/t/making-multiple-sql-queries-into-one/38292/7 "2020-09-02T12:16:13Z")

</div>

Yes. Trying to show machine Uptime/Downtime/Standby-conditions. Figured making a report first then will try making a graph… (that’ll take me forever). Never heard of SQL LEAD or LAG so looks like some more reading for me there also. Appreciate the insight.

---

<div class="post-metadata">

**Author:** ![martinel3746](https://avatars.discourse-cdn.com/v4/letter/m/e0b2c6/32.png) [@martinel3746](https://forum.inductiveautomation.com/u/martinel3746)\
**Post date:** [September 3, 2020, 7:47pm UTC](https://forum.inductiveautomation.com/t/making-multiple-sql-queries-into-one/38292/8 "2020-09-03T19:47:13Z")

</div>

Offhand it looks like UNION or UNION ALL between statements is all you need.

---

<div class="post-metadata">

**Author:** ![ilikebeer](https://avatars.discourse-cdn.com/v4/letter/i/eb8c5e/32.png) [@ilikebeer](https://forum.inductiveautomation.com/u/ilikebeer)\
**Post date:** [September 4, 2020, 7:26am UTC](https://forum.inductiveautomation.com/t/making-multiple-sql-queries-into-one/38292/9 "2020-09-04T07:26:37Z")

</div>

Thanks martinel3746. I just ended up using the 4 separate statements as suggested by bkarabinchak. although a lot more cumbersome, it works and that’s really good for a newbie like myself. I did make notes of your fix and will try it if/when I have to make another similar report.  
Thanks all!