17210

Combining Multiple SQL Queries Into Single Query

Question:

I have three queries as below and I need to combine them into one. Does any body know how to do that?

$myTasks = ""; $query = "SELECT taskID_PK " . "FROM tasks t " . "LEFT JOIN task_assignee ta ON ta.taskID_FK = t.taskID_PK ". "LEFT JOIN task_attachments a ON a.taskID_FK = t.taskID_PK ". "LEFT JOIN employee e ON e.employeeID_PK = t.assignByID_FK ". " WHERE TRUE AND isArchive != 1 AND taskStatus = 1 AND ta.clientID_FK = {$G_CLIENID} AND categoryID_FK !=0 group by taskID_PK "; $RawData = db::select($query); $myTasks= count($RawData); $closeTasks = ""; $query = "SELECT taskID_PK,taskTitle,taskDescn,categoryID_FK,priority,date_format(createDate, '%d/%m/%Y') as createDate,ticketID_FK, date_format(dueDate, '%d/%m/%Y') as dueDate ,assignByID_FK,createTime, taskStatus,closedDate,employeeName ,attachmentID_PK " . "FROM tasks t " . "LEFT JOIN task_assignee ta ON ta.taskID_FK = t.taskID_PK ". "LEFT JOIN task_attachments a ON a.taskID_FK = t.taskID_PK ". "LEFT JOIN employee e ON e.employeeID_PK = t.assignByID_FK ". " WHERE TRUE AND isArchive != 1 AND (taskStatus != 1 ){$taskCreateClause} AND categoryID_FK !=0 group by taskID_PK "; $RawData = db::select($query); $closeTasks = count($RawData); $workLoad =""; $query = "SELECT taskID_PK,taskTitle,taskDescn,categoryID_FK,priority,date_format(createDate, '%m/%d/%Y') as createDate,ticketID_FK, date_format(dueDate, '%m/%d/%Y') as dueDate ,assignByID_FK,createTime, taskStatus,closedDate,employeeName ,clientID_FK " . "FROM task_assignee ta " . "LEFT JOIN tasks t ON ta.taskID_FK = t.taskID_PK ". "LEFT JOIN employee e ON e.employeeID_PK = ta.clientID_FK ". " WHERE TRUE AND taskStatus = 1 "; $RawData = db::select($query); $workLoad = count($RawData);

Answer1:

Okay now that I understand the question, how about something like this:

select a.taskID_PK, (Select count(*)) . "FROM tasks t " . "LEFT JOIN task_assignee ta ON ta.taskID_FK = t.taskID_PK ". "LEFT JOIN task_attachments a ON a.taskID_FK = t.taskID_PK ". "LEFT JOIN employee e ON e.employeeID_PK = t.assignByID_FK ". " WHERE TRUE AND isArchive != 1 AND taskStatus = 1 AND ta.clientID_FK = {$G_CLIENID} AND categoryID_FK !=0 and task_ID_PK = a.taskID_PK) as Count1, group by taskID_PK .... from tasks

Select the main ID, then 3 independent select statements return a count and joined up with the ID at the beginning.

Answer2:

SELECT (SELECT(COUNT(DISTINCT t.taskID_PK)) FROM tasks t LEFT JOIN task_assignee ta ON ta.taskID_FK = t.taskID_PK LEFT JOIN task_attachments a ON a.taskID_FK = t.taskID_PK LEFT JOIN employee e ON e.employeeID_PK = t.assignByID_FK WHERE TRUE AND isArchive != 1 AND taskStatus = 1 AND categoryID_FK !=0)as activetasks, (SELECT(COUNT(DISTINCT t.taskID_PK)) FROM tasks t LEFT JOIN task_assignee ta ON ta.taskID_FK = t.taskID_PK LEFT JOIN employee e ON e.employeeID_PK = t.assignByID_FK WHERE TRUE AND isArchive != 1 AND taskStatus = 1 AND ta.clientID_FK=1 AND categoryID_FK !=0)as mytasks, (SELECT(COUNT(DISTINCT t.taskID_PK)) FROM tasks t LEFT JOIN task_assignee ta ON ta.taskID_FK = t.taskID_PK LEFT JOIN employee e ON e.employeeID_PK = t.assignByID_FK WHERE TRUE AND isArchive != 1 AND taskStatus != 1 AND categoryID_FK !=0)as cloastasks, (SELECT(COUNT(t.taskID_PK)) FROM task_assignee ta LEFT JOIN tasks t ON ta.taskID_FK = t.taskID_PK LEFT JOIN employee e ON e.employeeID_PK = ta.clientID_FK WHERE TRUE AND taskStatus = 1)as workload, (SELECT(COUNT(DISTINCT t.taskID_PK)) FROM tasks t LEFT JOIN task_assignee ta ON ta.taskID_FK = t.taskID_PK LEFT JOIN task_attachments a ON a.taskID_FK = t.taskID_PK LEFT JOIN employee e ON e.employeeID_PK = t.assignByID_FK WHERE TRUE AND isArchive = 1 AND categoryID_FK !=0)as archivetasks.

I combined my queries and I want to know is there any better way to combine those to improve efficiency?

Recommend

  • jQuery redirect to specific URL based on checkbox click category
  • How to index related documents in reverse direction in Ravendb
  • DirectShow (Stream.h) for Windows 10 Development
  • WP8.1 AppBarButton holding event
  • Set focus to first invalid form element in AngularJS
  • Python delete lines of text line #1 till regex
  • jQuery Orbit - How to make a Random Slideshow?
  • Can't delete or rename original file after resizing
  • Select options in sencha touch is not working for android
  • Django invalid literal for int() with base 10
  • ListItem.Attributes.Add not working
  • Diff between two dataframes in pandas
  • Should I or shouldn't I use the CachingConnectionFactory with hornetq 2.4.1
  • WPF - CanExecute dosn't fire when raising Commands from a UserControl
  • How to use RequestBodyAdvice
  • Jetty Server not starting: Unable to establish loopback connection
  • Body moving without any force applied? (Box2d)
  • does jqgrid support a multiple checkbox list for editing
  • Linq Objects Group By & Sum
  • java.lang.NoClassDefFoundError: com.parse.Parse$Configuration$Builder on below Lollipop versions
  • JFileChooser in front of fullscreen Swing application
  • Read text file and split every line in MSBuild
  • jQuery show() function is not executed in Safari if submit handler returns true
  • Align navbar back button on right side
  • HTML download movie download link
  • How to add a column to a Pandas dataframe made of arrays of the n-preceding values of another column
  • Modifying destination and filename of gulp-svg-sprite
  • How to handle AllServersUnavailable Exception
  • How to model a transition system with SPIN
  • VBA Convert delimiter text file to Excel
  • ORA-29908: missing primary invocation for ancillary operator
  • How to disable jQuery.jplayer autoplay?
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • embed rChart in Markdown
  • How to stop GridView from loading again when I press back button?
  • Bitwise OR returns boolean when one of operands is nil
  • sending mail using smtp is too slow
  • Busy indicator not showing up in wpf window [duplicate]
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • How can I use `wmic` in a Windows PE script?