Dear All,
I have worked out a hamfisted solution, but I believe that it works. However, there must be a solution that does it properly in one SQL statement:
1) Create temporary table (#tempDepart) with fields: ComputerGuid, ComputerName, DepartmentName
2) Run SQL Query to add the data for the machines that have Departments into the temporary table:
INSERT INTO #tempDepart
SELECT vc.Guid, vc.[Name], rd.[Name]
FROM vComputer vc
JOIN ResourceAssociation ra ON (vc.Guid = ra.ParentResourceGuid)
JOIN RM_ResourceDepartment rd ON (ra.ChildResourceGuid = rd.Guid)
WHERE ResourceAssociationTypeGuid = 'AC931247-CD6B-4597-B677-EAE84AA02355'
3) Run SQL query to add other computers (without department Resource Associations):
INSERT INTO #tempDepart
SELECT vc.[Name],
NULL
FROM vComputer vc
WHERE vc.Guid NOT IN (SELECT ComputerGuid IN #tempDepart)
4) Run the SQL Query to output the contents of the report:
SELECT ComputerName, DepartmentName FROM #tempDepart
5) Drop the temporary table (although this might be unnecessary):
DROP TABLE #tempDepart
As I said, it WORKS, but it is hamfisted. There should be a method to get the same results from the database without a Temporary table!
Kindest regards,
QuietLeni