Nested Query?

[eggy168]

Hi, I need two tables for the result. I can do it in three queries in Access, but I really don't know what should I do in SQL Server. I hope someone can guide me since I have so many queries that I need to convert from Access to SQL.
Thank You.

TableA
Name|Subject|Course|Sort|Mark
Row1 YY|MathI|Math010|01|75
Row2 YY|MathI|Math011|02|65
Row3 YY|MathI|Math014|15|50
Row4 YY|MathI|Math015|04|80
Row5 YY|MathI|Math035|20|90
Row6 ZZ|MathII|Math010|01|75
Row7 ZZ|MathII|Math011|02|65

Query 1:
Select
Name,
Subject,
Course,
Sort,
SUM(Mark) As Mark
From
TableA
WHERE Sort >=N'01' and <=N'15'
GOUP BY Name, Subject, Course, Sort

Result:
Name|Subject|Course|Sort|Mark
Row1 YY|MathI|Math010|01|75
Row2 YY|MathI|Math011|02|65
Row3 YY|MathI|Math014|15|50
Row4 YY|MathI|Math015|04|80
Row6 ZZ|MathII|Math010|01|75
Row7 ZZ|MathII|Math011|02|65

Query 2:

Select
Name,
Subject,
SUM(Mark) As Mark
From
TableA
GOUP BY Name, Subject


Result:
Name|Subject||Mark
Row1 YY|MathI|270
Row2 ZZ|MathII|140

Table B
Name|Subject|LastYrMark
Row1 YY|Math1|150
Row2 ZZ|Math2|500

Join Query 2 & Table B

Select
Query2.Name,
Query2.Subject,
SUM(Query2.Mark) As Mark
TableB.LastYrMark
From
Query2
Left Outer Join
Query2.Name = TableB.Name AND
Query2.Subject = TableB.Subject
GOUP BY Query2.Name, Query2.Subject

FinalResult:
Name|Subject||Mark|LastYearMark
Row1 YY|MathI|270|150
Row2 ZZ|MathII|140|500

Thanks for your help.

 

[Qik3Coder]

You would normally create those "query" objects as views.
You can do those selects "in-line" though.

Syntax sample:

Select b.*
FROM
(SELECT * from TableA) as b
Left JOIN
(SELECT * from TableB) as c ON
b.col1 = c.col1
Where tra la la


HTH,
Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this "[red]there was an error[/red]" [black]crap[/black]

 

[eggy168]

Hi,
I ended up using this stored procedure/nested query in asp.net with parameter. That's why I think I need this to be build as a nested query in stored procedure.
I will try your example.
Thank

 

[Qik3Coder]

What I was indicating is that you can use some constructs in SQL to ease/reduce duplicate efforts.

I have a preference for TABLE FUNCTIONS (sample below)
These give you parameters and a structured result set.
You will still want a "wrapping" stored procedure for code access, but the table functions are great for assembling pieces.

Select em.EmpID, em.Name, Sum(py.TotalPayout)
FROM dbo.fnGetMyEmployees('SomeCompany') em
JOIN dbo.fnGetMyPaychecks(GetDate()-30, GetDate()) py ON
em.EmpID = py.EmpID
GROUP BY em.EmpID, em.Name

Lod

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this "[red]there was an error[/red]" [black]crap[/black]

 

[eggy168]

Hi,
I still can not figure out what should I do in my original question. It keeps having error message.
Is there anyway you can help me for my origianl question?
Thanks

 

[Qik3Coder]

I was a little thrown, you have query 1 in there, which isn't used.

Wrote this in the message editor, but it should be close.

Select Query2.Name, Query2.Subject, SUM(Query2.Mark) As Mark   TableB.LastYrMark
FROM (
   Select Name, Subject, SUM(Mark) As Mark
   From TableA
   GROUP BY Name, Subject) as Query2
LEFT JOIN tableB ON
   Query2.Name = TableB.Name
   AND Query2.Subject = TableB.Subject
GROUP BY Query2.Name, Query2.Subject

Lod

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this "[red]there was an error[/red]" [black]crap[/black]

 

[gmmastros]

You would normally create those "query" objects as views.
You can do those selects "in-line" though.

Allow me a moment to explain the 2 options mentioned by Qik3Coder.

A view (in SQL Server) is basically a query that you can save in the database. You can then reference the view similarly to a table.

To create a view, I usually open SQL Server Management Studio and write the query first. I run the query to make sure it returns the results I want. Once I am satisfied with the query, I add the parts that make it a view. You've already done step one. To make that query in to a view, open a new query window and copy/paste this:

Create View Query1
AS
Select 
   Name, 
   Subject, 
   Course, 
   Sort, 
   SUM(Mark) As Mark
From 
   TableA
WHERE Sort >=N'01' and Sort <=N'15'
GROUP BY Name, Subject, Course, Sort

Press the F5 key. It may not appear as though anything has happened, but (unless you get an error message), SQL Server has created the view for you. This means you can open a new query window and type:

[tt][blue]Select * From Query1[/blue][/tt]

Now, when you run the query, you will see the results. Notice how you select from the view as though it were a table? Pretty cool.

Now you can create your other query, like this:

Create View Query2
AS
Select 
   Name, 
   Subject, 
   SUM(Mark) As Mark
From 
   Query1
GROUP BY Name, Subject

Then, your final query can use it like this:

Select 
   Query2.Name, 
   Query2.Subject, 
   SUM(Query2.Mark) As Mark
   TableB.LastYrMark
From 
   Query2 
     Left Outer Join TableB ON
      Query2.Name = TableB.Name AND
      Query2.Subject = TableB.Subject
GROUP BY Query2.Name, Query2.Subject, TableB.LastYrMark

Note that the queries you originally had are essentially unchanged. The only thing I've done so far is to show you how to create a view. At this point, I would like to caution about views. Views are great, and I do use them, but you need to be a little careful about OVER-USING them. Let me explain...

When SQL Server runs a query, it first determines the best way to do that. This step involves analyzing the query and your database (indexes, statistics, etc) so that it can generate an execution plan that returns the correct results in the shortest time possible. During this step, SQL Server will often analyze the query as though you didn't use the view so that it looks at the underlying data to determine the fastest way to execute the query. When your query uses nested views, at some point SQL Server cannot determine the optimal execution plan and your performance will suffer. Nesting views a couple layers deep should not be a problem for the optimizer, but if you go too many layers, you may eventually have a problem. I've heard from some DBA's where views were nest 20 or 30 layers deep and the top most query took minutes to execute. By re-writing the query to not use views, the same results were obtained within milliseconds (instead of minutes).

Qik3Coder also mentioned about writing the queries "in-line". The technique he described is known as "derived tables". If you want to learn more about this technique, google for "SQL Server derived tables", and you will get tons of results.

Your same query written as a derived table would look like this:

Select 
   Query2.Name, 
   Query2.Subject, 
   SUM(Query2.Mark) As Mark,
   TableB.LastYrMark
From (
     Select 
        Name, 
        Subject, 
        SUM(Mark) As Mark
     From [!]([/!]
          Select 
             Name, 
             Subject, 
             Course, 
             Sort, 
             SUM(Mark) As Mark
          From 
             TableA
          WHERE Sort >=N'01' and Sort <=N'15'
          GROUP BY Name, Subject, Course, Sort
     	[!]) As A[/!]
     GROUP BY Name, Subject
     ) As Query2
     Left Outer Join TableB
      ON Query2.Name = TableB.Name AND
      Query2.Subject = TableB.Subject
GROUP BY Query2.Name, Query2.Subject, TableB.LastYrMark

Note that the query is a lot more complicated when viewing it this way, but it is not based on nested views.

Notice the red parenthesis in the code above. To use a derived table, you need the red parts or else you will get an error.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[eggy168]

Thanks everyone. I guess I have a clue on how to write the query based on all of your examples. I'll check tomorrow and I will let you guys know the result.
Thanks a lot.