{"id":1986,"date":"2018-05-28T15:55:23","date_gmt":"2018-05-28T20:55:23","guid":{"rendered":"http:\/\/bluegalaxy.info\/codewalk\/?p=1986"},"modified":"2018-05-29T16:30:52","modified_gmt":"2018-05-29T21:30:52","slug":"mysql-run-multiple-queries-one","status":"publish","type":"post","link":"https:\/\/bluegalaxy.info\/codewalk\/2018\/05\/28\/mysql-run-multiple-queries-one\/","title":{"rendered":"MySQL: How to run multiple queries in one"},"content":{"rendered":"

Let’s say we have a couple of simple SQL queries, one of which counts the total number of job hours:<\/p>\n

select sum(Hours) as totalCount from job_details where Job_ID = 42504;<\/pre>\n
and another query that sums the total number of job hours that have been completed:<\/p>\n
select sum(Hours) as completedCount from job_details where Job_ID = 42504 and Completed = 1;<\/pre>\n
If we want to combine both of these queries into a single query, we can! The trick is to use aliases for both of the selects.<\/p>\n
For example, here I have used the aliases “as a” and “as b”:<\/p>\n
select \r\n    totalCount, \r\n    completedCount \r\nfrom \r\n    (select sum(Hours) as totalCount from job_details where Job_ID = 42504) as a, \r\n    (select sum(Hours) as completedCount from job_details where Job_ID = 42504 and Completed = 1) as b;\r\n<\/pre>\n
\"\"<\/p>\n
Here is an example of wrapping 5 SQL queries into one:<\/p>\n
select \r\n\ttotal, \r\n\tcompleted, \r\n\tdept_id, \r\n\tdeptName,\r\n\tschedStart,\r\n\tschedEnd,\r\n\tnumTasks\r\nfrom \r\n\t(select \r\n\t\tsum(details.Hours) as total \r\n\tfrom \r\n\t\tjob_details details, \r\n\t\tjob_schedule sched\r\n\twhere \r\n\t\tdetails.Job_ID = '41230' and\r\n\t\tsched.Shop_ID = '9553' and\r\n\t\tdetails.Job_ID = sched.Job_ID) as a,\r\n\r\n\t(select \r\n\t\tsum(details.Hours) as completed \r\n\tfrom \r\n\t\tjob_details details, \r\n\t\tjob_schedule sched\r\n\twhere \r\n\t\tdetails.Job_ID = '41230' and \r\n\t\tdetails.Completed = 1 and\r\n\t\tsched.Shop_ID = '9553' and\r\n\t\tdetails.Job_ID = sched.Job_ID) as b,\r\n\r\n\t(select \r\n\t\tstore.Dept_ID as dept_id, \r\n\t\twebsite.Department as deptName \r\n\tfrom \r\n\t\tstore.job_details store, \r\n\t\tjob_schedule sched,\r\n\t\twebsite.customer_service_department website \r\n\twhere \r\n\t\tstore.Job_ID = '41230' and \r\n\t\tsched.Shop_ID = '9553' and\r\n\t\tstore.Completed = 0 and \r\n\t\tstore.Dept_ID = website.ID and\r\n\t\tstore.Job_ID = sched.Job_ID) as c,\r\n\r\n\t(select \r\n\t\tScheduled_Start as schedStart,  \r\n\t\tScheduled_End as schedEnd\r\n\t\tfrom job_schedule sched\r\n\twhere \r\n\t\tJob_ID = '41230' and \r\n\t\tShop_ID = '9553') as d,\r\n\r\n\t(select \r\n\t\tcount(Description) as numTasks \r\n\tfrom \r\n\t\tjob_details \r\n\twhere \r\n\t\tJob_ID = '41230') as e;<\/pre>\n
\"\"<\/p>\n
Notes:<\/p>\n