SQL - Finding common rows based on a column (intersect)

sql-server, sql-server-2008, t-sql

Solution

I think this should do it for the general case:

SELECT AppName, Version, COUNT(DISTINCT(MachineName)) AS MachineCount
FROM InstalledApps
GROUP BY
  AppName, Version
HAVING
  COUNT(DISTINCT(MachineName)) > 1

Problem

Given the following table: InstalledApps ``` MachineName | AppName | Version ------------------------------- machine01 | App01 | 1.1 machine01 | App02 | 1.0 machine01 | App03 | 1.5 machine02 | App04 | 2.0 machine02 | App02 | 1.0 machine02 | App01 | 1.1 machine03 | App06 | 7.9.9 machine03 | App01 | 1.1 machine03 | App07 | 11.5 machine03 | App02 | 1.0 ``` I need to select `Apps` which are common to given "n" machines. It's an intersect between "n" sets. However, I'm having a hard time coming up with a decent query. I know that I can do something like this: ``` SELECT AppName, Version FROM InstalledApps WHERE MachineName = 'machine01' INTERSECT SELECT AppName, Version FROM InstalledApps WHERE MachineName = 'machine02' ``` That will give me ``` AppName | Version ------------------ App01 | 1.1 App02 | 1.0 ``` However, is there a way I can do it without having to know the number of machines upfront? Something like ``` SELECT...... FROM InstalledApps WHERE MachineName IN ('machine01',...'machine99') ```

Original source