Solved

SQL Nested Queries

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL join help to a thrid table 51 76
SSRS Highlight Specific Column 3 55
Optimizing a query 3 34
Chrome SSRS print Functionality 1 20
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

895 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

19 Experts available now in Live!

Get 1:1 Help Now