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
rams - 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