Solved

SQL Nested Queries

Posted on 2009-05-15
3
1,067 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 250 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now