Solved

SQL Nested Queries

Posted on 2009-05-15
3
1,073 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

752 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