• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1089
  • Last Modified:

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

0
ryan_uk
Asked:
ryan_uk
  • 2
1 Solution
 
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
 
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
 
ryan_ukAuthor Commented:
Genius!  Thanks Greg.  Works exactly as posted no interpretation or anything required.

Thanks,

Ryan
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now