SQL Nested Queries

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

ryan_ukAsked:
Who is Participating?
 
JestersGrindCommented:
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
 
JestersGrindCommented:
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
 
ryan_ukAuthor Commented:
Genius!  Thanks Greg.  Works exactly as posted no interpretation or anything required.

Thanks,

Ryan
0
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.

All Courses

From novice to tech pro — start learning today.