Improve company productivity with a Business Account.Sign Up

x
?
Solved

SQL Nested Queries

Posted on 2009-05-15
3
Medium Priority
?
1,093 Views
Last Modified: 2012-08-13
Hi all,

I have a SQL query.  I *think* the answer is nested queries but I'm not sure.  Essentially I have two tables:

vComputer - Gives me PC Names
Inv_Aex_OS_Add_Remove_Programs - Gives me Add/Remove Programs Info

I need a report which shows me PC Names | MS Office Version where the PC has more than one copy of Office installed.

So for example PC123 has Office 2003 and Office 2007 installed.  I need the report to show me:

PC123 | Office 2003
PC123 | Office 2007

The code shown here presents me all the PC Names and the version of Office installed but I want to limit it to where there are two instances of the PC Name (and therefore two copies of Office installed).  I don't want to see the machines which have only one copy installed.

I tried using a Count and Having clauses but using a count in this code but it just returns a 1 value for each row - which I understand why.  Ultimately I can take the results from this code and place in in another table and then run another query on that table to get the answer I want but I need to give this to non-techies and they won't understand the concept of having to run two reports to get one answer!

Any help appreciated!
SELECT comp.[Name], arp.[Name] AS 'Product', arp.[Version]
FROM vComputer comp             
LEFT OUTER JOIN Inv_Aex_OS_Add_Remove_Programs arp ON arp.[_ResourceGuid] = comp.[Guid]             
WHERE arp.[Publisher] LIKE '%Microsoft%'             
AND arp.[Name] LIKE '%Office%'        
AND (arp.[Name] LIKE '%standard%' OR arp.[Name] LIKE '%prof%')             
             
GROUP BY comp.[Name], arp.[Name], arp.[Version]
ORDER BY comp.[Name] asc, arp.[Name] asc, arp.[Version] desc

Open in new window

0
Comment
Question by:ryan_uk
  • 2
3 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 24394698
Try this:

Greg

;WITH CTE
 
AS
(
SELECT comp.[Name], arp.[Name] AS 'Product', arp.[Version]
FROM vComputer comp             
LEFT OUTER JOIN Inv_Aex_OS_Add_Remove_Programs arp ON arp.[_ResourceGuid] = comp.[Guid]             
WHERE arp.[Publisher] LIKE '%Microsoft%'             
AND arp.[Name] LIKE '%Office%'        
AND (arp.[Name] LIKE '%standard%' OR arp.[Name] LIKE '%prof%')             
             
GROUP BY comp.[Name], arp.[Name], arp.[Version]
ORDER BY comp.[Name] asc, arp.[Name] asc, arp.[Version] desc
)
SELECT * 
FROM CTE
WHERE comp.[Name] IN(SELECT comp.[Name] FROM CTE GROUP BY comp.[Name] HAVING COUNT(comp.[Name]) > 1)

Open in new window

0
 
LVL 21

Accepted Solution

by:
JestersGrind earned 1000 total points
ID: 24394720
Oops! Remove the ORDER BY like this.

Greg



;WITH CTE
AS
(
SELECT comp.[Name], arp.[Name] AS 'Product', arp.[Version]
FROM vComputer comp             
LEFT OUTER JOIN Inv_Aex_OS_Add_Remove_Programs arp ON arp.[_ResourceGuid] = comp.[Guid]             
WHERE arp.[Publisher] LIKE '%Microsoft%'             
AND arp.[Name] LIKE '%Office%'        
AND (arp.[Name] LIKE '%standard%' OR arp.[Name] LIKE '%prof%')             
             
GROUP BY comp.[Name], arp.[Name], arp.[Version]
)
SELECT * 
FROM CTE
WHERE [Name] IN(SELECT [Name] FROM CTE GROUP BY [Name] HAVING COUNT([Name]) > 1)

Open in new window

0
 

Author Closing Comment

by:ryan_uk
ID: 31581884
Genius!  Thanks Greg.  Works exactly as posted no interpretation or anything required.

Thanks,

Ryan
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Did you know PowerShell can save you time with SaaS platforms? Simply leverage RESTfulAPIs to build your own PowerShell modules. These will kill repetitive tickets and tabs, using the command Invoke-RestMethod. Tune into this webinar to learn how…

606 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