Solved

SQL Nested Queries

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

809 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