Select Query Assistance

Transact-SQL
1

I am learning sql querying and I am making my own scenarios and attempting to pull data out of tables I have created but I am hitting a wall. Apologies this is probably simplistic query but I am hoping you can enlighten me as to what I am doing incorrectly and to provide assistance. The Premise is I have a list of PCs that I want to rebuild but only based on if all apps that are installed on them are windows 10 compatible.

I have a few tables.
Tbl_Windows_7_PCs Fields are MachineName IPAddress AssignedUser Application

Now this table shows multiple rows it has no primary key. An example is as follows

Pc001 10.0.0.20 Joe Bloggs Ms Word 2016
Pc001 10.0.0.20 Joe Bloggs Ms Excel 2016
Pc001 10.0.0.20 Joe Bloggs Ms Access 2016
Pc002 10.0.0.34 Jane Smith Ms Excel 2016
Pc002 10.0.0.34 Jane Smith Sage
Pc002 10.0.0.34 Jane Smith Adobe Acrobat
I have another table called Windows Compatible Aplications

Once again no primary key

Tbl_Win10_Compatible_Apps

Fields are but one

Application

Values are

MS Excel 2016
Sage
Adobe Acrobat

So I want to produce a query that selects only the machine name and the assigneduser from tbl_Windows_7_PCs Where all applications that are installed are present in Tbl_Win10_Compatible_Apps.

The kicker is I don’t want to pull back all of the rows I just want to show the machine and user (singular) once ALL apps on a pc are win 10 compatible I don’t want to show if only some are.

As I am learning would appreciate if anyone has the solution to explain how they arrived at the solution.

2
  • do a "left outer join" - this way, when field application in table tbl_win10_compatible_apps is null, we found an application that is not compatible
  • group by machinename
  • use having clause to sum number of incompatible aplications (this should be 0)
select a.machinename
  from tbl_windows_7_pcs as a
       left outer join tbl_win10_compatible_apps as b
                    on b.application=a.application
 group by a.machinename
 having sum(case when b.application is null then 1 else 0 end)=0
;
3

And to show there is more than one way to skin a cat:

Select Distinct
	IP, FirstName, Surname
From #INSTALLS
Where Application = ANY(
	Select Application From #INSTALLS
	Except Select Application From #WIN10COMPAT
	)

Here I pick distinct details from the first table where and application in any of the rows matches an entry in a list made up of all the applications, except the Windows 10 compatible ones.

4

Thank-you for this. Worked really well.