Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Transact SQL nested select

Posted on 2008-10-06
11
Medium Priority
?
961 Views
Last Modified: 2008-10-06
Dont know if I am going about this the right way. I am trying:

SELECT     Industries.IndustryDesc, sum(LinkClientItems.Value) as CoreExpenses,

(
SELECT    sum(LinkClientItems.Value) as Sell
FROM         Industries INNER JOIN
                      Categories ON Industries.IndustryID = Categories.IndustryID INNER JOIN
                      Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
                      LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE     (LinkClientItems.ItemUseID = 2)

)as Sell
FROM         Industries INNER JOIN
                      Categories ON Industries.IndustryID = Categories.IndustryID INNER JOIN
                      Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
                      LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE     (LinkClientItems.ItemUseID = 1)

group by industries.industrydesc


This is giving :

Advertising; Media; Printi      700.00      223.00
Building; Construction      5300.00      223.00
Manufacturing      500.00      223.00
Professional                            500.00      223.00

The first sum column is correct, but the second is simply the first sum repeated. Not grouping properly (or something). My knowledge of SQL is not great and I dont usually go past simple joins for queries. Any help would be appreciated.
0
Comment
Question by:subversivetech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22655181
Think you want to filter the subquery based on the current industry you are on.
SELECT     a.IndustryDesc, sum(LinkClientItems.Value) as CoreExpenses,
 
(
SELECT    sum(LinkClientItems.Value) as Sell
FROM         Industries b INNER JOIN
                      Categories ON Industries.IndustryID = Categories.IndustryID INNER JOIN
                      Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
                      LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE     (LinkClientItems.ItemUseID = 2 AND a.IndustryDesc = b.IndustryDesc)
 
)as Sell
FROM         Industries a INNER JOIN
                      Categories ON a.IndustryID = Categories.IndustryID INNER JOIN
                      Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
                      LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE     (LinkClientItems.ItemUseID = 1)
 
group by a.industrydesc

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22655183
Think you want to filter the subquery based on the current industry you are on.
SELECT     a.IndustryDesc, sum(LinkClientItems.Value) as CoreExpenses,
 
(
SELECT    sum(LinkClientItems.Value) as Sell
FROM         Industries b INNER JOIN
                      Categories ON Industries.IndustryID = Categories.IndustryID INNER JOIN
                      Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
                      LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE     (LinkClientItems.ItemUseID = 2 AND a.IndustryDesc = b.IndustryDesc)
 
)as Sell
FROM         Industries a INNER JOIN
                      Categories ON a.IndustryID = Categories.IndustryID INNER JOIN
                      Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
                      LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE     (LinkClientItems.ItemUseID = 1)
 
group by a.industrydesc

Open in new window

0
 

Author Comment

by:subversivetech
ID: 22655230
Executing that gives:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Industries.IndustryID" could not be bound.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22655264
Sorry I missed one.  You have to qualify all the references of Industries by aliases if used and used aliases to tell which one I intend since crossing between outer query and inner one in where clause.
SELECT     a.IndustryDesc, sum(LinkClientItems.Value) as CoreExpenses,
 
(
SELECT    sum(LinkClientItems.Value) as Sell
FROM         Industries b INNER JOIN
                      Categories ON b.IndustryID = Categories.IndustryID INNER JOIN
                      Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
                      LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE     (LinkClientItems.ItemUseID = 2 AND a.IndustryDesc = b.IndustryDesc)
 
)as Sell
FROM         Industries a INNER JOIN
                      Categories ON a.IndustryID = Categories.IndustryID INNER JOIN
                      Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
                      LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE     (LinkClientItems.ItemUseID = 1)
 
group by a.industrydesc

Open in new window

0
 

Author Comment

by:subversivetech
ID: 22655263
This works the way I want on some test data:

SELECT industry, sum(Value) as Boating,
(
SELECT sum(Value) as Cars
FROM Test
where category = 'Cars'
) as Cars

FROM Test
where category = 'Boating'

group by industry
This is against a single table with 3 fields:
Industry , category, value
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22655273
That will get you exactly what you have above.  If that is what you intend then the result is correct.

You are getting a sum of all the values where category = 'Cars' regardless of industry or whatever else is being used in outer query.  If you have multiple rows in the outer query, all the rows will have the same sum.  

If you want different sums by row, you must use a value from each row as the criteria for the sum.

Hope that helps.
0
 

Author Comment

by:subversivetech
ID: 22655316
You solution is very close to what I am looking for. It is looking good, but it does not give rows that have a null value for the 'coreexpenses' column. Any idea to make sure they are included?
Your solution gives:
Advertising; Media; Printing  700.00   NULL
Building; Construction         5300.00   23.00
Manufacturing                        500.00     NULL
Professional                          500.00    NULL
But it should include
Health                                   null            200
Cheers for the help!
0
 

Author Comment

by:subversivetech
ID: 22655402
Further,
I need every IndustryDesc returned regardless of whether there are any LinkClientItems for that Industry
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22655535
Well for that you needed a LEFT JOIN in your original query.
SELECT     a.IndustryDesc, sum(LinkClientItems.Value) as CoreExpenses,
 
(
SELECT    sum(LinkClientItems.Value) as Sell
FROM         Industries b INNER JOIN
                      Categories ON b.IndustryID = Categories.IndustryID INNER JOIN
                      Items ON Categories.CategoryID = Items.CategoryID INNER JOIN
                      LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE     (LinkClientItems.ItemUseID = 2 AND a.IndustryDesc = b.IndustryDesc)
 
)as Sell
FROM         Industries a INNER JOIN
                      Categories ON a.IndustryID = Categories.IndustryID INNER JOIN
                      Items ON Categories.CategoryID = Items.CategoryID LEFT JOIN
                      LinkClientItems ON Items.ItemID = LinkClientItems.ItemID
WHERE     (IsNull(LinkClientItems.ItemUseID,1) = 1)
 
group by a.industrydesc

Open in new window

0
 

Author Comment

by:subversivetech
ID: 22655613
Mate you are a legend!
Thanks from down under. I am finding it difficult to get my head around the more advanced T-SQL. I find that the Microsoft docs and MSDN alway use overly complex examples. Not sure if you could recommend a resource to use as a reference as I work my way up?
 
Cheers.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22655745
I usually find the "In A Nutshell" books from O'Reilly and/or the Wrox books pretty helpful.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question