SELECT DISTINCT a.DepartmentID, a.DepartmentName, b.MachineName 
      FROM tblDepartment a
 INNER JON tblMachines b
	ON a.DepartmentID = b.DepartmentID
 LEFT JOIN tblSoftware c
	ON b.MachineId = c.fkMachineID
     WHERE NOT EXISTS (SELECT DISTICT SoftwareID, Caption, Version 
		    FROM tblSoftware d
		WHERE c.SoftwareID = d.SoftwareID)

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE mytblDepartment99(DepartmentID char(1), DepartmentName varchar(10))
CREATE TABLE mytblMachines99(MachineID char(1), MachineName varchar(10), fkDepartmentID char(1))
CREATE TABLE mytblSoftware99(SoftwareID char(1), Caption varchar(10), Version int, fkMachineID char(1))
GO

INSERT INTO mytblDepartment99(DepartmentID, DepartmentName)
SELECT 'A', 'Dept A' UNION ALL
SELECT 'B', 'Dept B'

INSERT INTO mytblMachines99(MachineID, MachineName, fkDepartmentID)
SELECT 'x', 'Machine x', 'A' UNION ALL
SELECT 'y', 'Machine y', 'B'

INSERT INTO mytblSoftware99(SoftwareID, Caption, Version, fkMachineID)
SELECT 't','Soft t',1,'x' UNION ALL
SELECT 'd','Soft d',1,'y' UNION ALL
SELECT 'e','Soft e',1,'y' UNION ALL
SELECT 'f','Soft f',1,'y'
GO



SELECT DISTINCT xxx.DepartmentID, xxx.DepartmentName, xxx.MachineName, d.SoftwareID AS MissingSoftware
      FROM ( SELECT DISTINCT a.DepartmentID, a.DepartmentName, b.MachineID, b.MachineName, c.SoftwareID
	       FROM mytblDepartment99 a
	 INNER JOIN mytblMachines99 b
		 ON a.DepartmentID = b.fkDepartmentID
	 INNER JOIN mytblSoftware99 c
		 ON b.MachineId = c.fkMachineID) AS XXX
CROSS JOIN (SELECT DISTINCT SoftwareID FROM mytblSoftware99) AS d
     WHERE XXX.SoftwareId <> d.SoftwareID
       AND NOT EXISTS (SELECT * 
			 FROM mytblSoftware99 e 
			WHERE e.fkMachineId = xxx.MachineID 
			  AND e.SoftwareId  = xxx.SoftwareID)
GO

DROP TABLE mytblDepartment99
DROP TABLE mytblMachines99
DROP TABLE mytblSoftware99
GO