CODEWITHSUNDEEP
sqlperformancesubquery
Joseph.Scott.Garza
Joseph.Scott.Garza

Reputation: 133

How to nest multiple SQL queries?

Can someone show me how to nest these queries into one entire query? Right now, Im just running each of these (up to 10) one at a time but I want to learn how to run this query in one single shot. 

Dim queryString1 As String = 
"SELECT EMPLOYEE_NAME, AVG(EFFICIENCY_YIELD) AS YIELD 
 FROM dbo.APE_BUSDRIVER_MAIN 
 WHERE APE_AREA_OBJID = " & lblAreaOBJID.Text & " 
 AND ACTIVE = 1 
 GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & ""


Dim queryString2 As String = 
"SELECT EMPLOYEE_NAME, AVG(EFFICIENCY_YIELD) AS YIELD 
 FROM dbo.APE_BUSDRIVER_MAIN 
 WHERE APE_AREA_OBJID = " & lblAreaOBJID.Text & " 
 AND ACTIVE = 1  
 AND EMPLOYEE_NAME <> '" & LblStckRnk1.Text & "' 
 GROUP BY EMPLOYEE_NAME 
 ORDER BY YIELD " & lblSortOrder.Text & ""


Dim queryString3 As String = 
"SELECT EMPLOYEE_NAME, AVG(EFFICIENCY_YIELD) AS YIELD 
 FROM dbo.APE_BUSDRIVER_MAIN 
 WHERE APE_AREA_OBJID = " & lblAreaOBJID.Text & " 
 AND ACTIVE = 1  
 AND EMPLOYEE_NAME <> '" & LblStckRnk1.Text & "' 
 AND EMPLOYEE_NAME <> '" & LblStckRnk2.Text & "' 
 GROUP BY EMPLOYEE_NAME 
 ORDER BY YIELD " & lblSortOrder.Text & ""

Upvotes: 0

Views: 233

Answers (2)

Amy A
Amy A

Reputation: 131

Let's check what this queries are returning:

(all three uses the same group by and the same order )

1- Returns all the "active" employees at "areaFooBar"

2- Returns all the "active" employees at "areaFooBar" MINUS one employee

3- Returns all the "active" employees at "areaFooBar" MINUS two employee

You can do this kind of filtering in the code. You don't have to run query 2 or query 3. Re-iterate the resultset of query 1 and filter the employee you do not want to include.

You can have to read it from database each time, then you can use one queryString if you add one more parameter to your query i.e.: comma-separated-list.Text 

Dim queryString1 As String = 
"
 SELECT EMPLOYEE_NAME, AVG(EFFICIENCY_YIELD) AS YIELD 
 FROM dbo.APE_BUSDRIVER_MAIN 
 WHERE APE_AREA_OBJID = " & lblAreaOBJID.Text & " 
 AND ACTIVE = 1 
 EMPLOYEE_NAME NOT IN ( " & comma-separated-list.Text & " ) 
 GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & ""

Upvotes: 1

tgolisch
tgolisch

Reputation: 6734

I would use a few UNION statements:

Dim queryString1 As String = "SELECT EMPLOYEE_NAME, AVG(EFFICIENCY_YIELD) AS YIELD
  FROM dbo.APE_BUSDRIVER_MAIN WHERE APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND ACTIVE = 1 
  GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & vbCrLf & _

" UNION " & vbCrLf & _

"SELECT EMPLOYEE_NAME, AVG(EFFICIENCY_YIELD) AS YIELD 
  FROM dbo.APE_BUSDRIVER_MAIN 
  WHERE APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND ACTIVE = 1 
  AND EMPLOYEE_NAME <> '" & LblStckRnk1.Text & "' 
  GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text & vbCrLf & _

" UNION " & vbCrLf & _

"SELECT EMPLOYEE_NAME, AVG(EFFICIENCY_YIELD) AS YIELD 
  FROM dbo.APE_BUSDRIVER_MAIN 
  WHERE APE_AREA_OBJID = " & lblAreaOBJID.Text & " AND ACTIVE = 1 
  AND EMPLOYEE_NAME <> '" & LblStckRnk1.Text & "' AND EMPLOYEE_NAME <> '" & LblStckRnk2.Text & "' 
  GROUP BY EMPLOYEE_NAME ORDER BY YIELD " & lblSortOrder.Text

(of course, you will want to remove the extra line-breaks that I added for readability)

Upvotes: 1

Related Questions