Client Management Suite

Hi,

Can someone tell me what is wrong with my JOINS as I think that is preventing this query from executing.

SELECT DISTINCT    vComputer.Name AS 'Computer Name', vComputer.[IP Address], vComputer.[OS Name], vComputer.[OS Revision], vComputer.[System Type], vHWComputerSystem.Manufacturer,
                      vHWComputerSystem.Model, vHWComputerSystem.[Identifying Number] AS 'Serial Number', vHWProcessor.Model AS 'Processor',
                      vHWComputerSystem.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)', vComputer.[User] AS 'Primary Owner'

FROM               CollectionMembership INNER JOIN vComputer ON CollectionMembership.ResourceGuid = vComputer.Guid INNER JOIN
                      vCollection INNER JOIN Collection ON vCollection.Guid = Collection.Guid ON CollectionMembership.CollectionGuid = Collection.Guid
                      vComputer LEFT OUTER JOIN
                      ResourceAssociation AS loc ON (loc.ChildResourceGuid = vComputer.Guid OR
                      loc.ParentResourceGuid = vComputer.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C' LEFT OUTER JOIN
                      vItem AS I ON loc.ChildResourceGuid = I.Guid LEFT OUTER JOIN
                      vHWProcessor ON vComputer.Guid = vHWProcessor._ResourceGuid LEFT OUTER JOIN
                      vHWComputerSystem ON vComputer.Guid = vHWComputerSystem._ResourceGuid
WHERE     (vComputer.IsManaged = 1) AND (vComputer.[OS Name] NOT LIKE '%Server%') AND (vCollection.Name = '.All Computers without SEP')
ORDER BY 'Computer Name'

Your 2nd INNER JOIN is on vCollection, but you are not telling it what to join on. 

Should be:

INNER JOIN vCollection

on something = something

I am trying to combine two queries to produce a desired result. The JOINS tend to throw me for a loop. Here is both queries if you wouldn't mind telling me how to merge them. I would like to end using the top query. Thanks.

SELECT DISTINCT    vComputer.Name AS 'Computer Name', vComputer.[IP Address], vComputer.[OS Name], vComputer.[OS Revision], vComputer.[System Type], vHWComputerSystem.Manufacturer,
                      vHWComputerSystem.Model, vHWComputerSystem.[Identifying Number] AS 'Serial Number', vHWProcessor.Model AS 'Processor',
                      vHWComputerSystem.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)', vComputer.[User] AS 'Primary Owner'
FROM         vComputer LEFT OUTER JOIN
                      ResourceAssociation AS loc ON (loc.ChildResourceGuid = vComputer.Guid OR
                      loc.ParentResourceGuid = vComputer.Guid) AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C' LEFT OUTER JOIN
                      vItem AS I ON loc.ChildResourceGuid = I.Guid LEFT OUTER JOIN
                      vHWProcessor ON vComputer.Guid = vHWProcessor._ResourceGuid LEFT OUTER JOIN
                      vHWComputerSystem ON vComputer.Guid = vHWComputerSystem._ResourceGuid
WHERE     (vComputer.IsManaged = 1) AND (vComputer.[OS Name] NOT LIKE '%Server%')
ORDER BY 'Computer Name'

SELECT DISTINCT vComputer.Guid, vComputer.Name AS 'Computer Name', vComputer.[User] AS 'Primary Owner', vComputer.[OS Name], vComputer.[OS Revision], vCollection.Description

FROM CollectionMembership INNER JOIN

vComputer ON CollectionMembership.ResourceGuid = vComputer.Guid INNER JOIN

vCollection INNER JOIN

Collection ON vCollection.Guid = Collection.Guid ON CollectionMembership.CollectionGuid = Collection.Guid

where vCollection.Name = '.All Computers without SEP'

Is this what you are looking for? Basically you want the top query, but want the ability to filter based on collection memberships.

select distinct vComputer.name as 'Computer Name',
    vComputer.[IP Address],
    vComputer.[OS Name],
    vComputer.[OS Revision],
    vComputer.[System Type],
    vHWComputerSystem.Manufacturer,
    vHWComputerSystem.Model,
    vHWComputerSystem.[Identifying Number] as 'Serial Number',
    vHWProcessor.Model as 'Processor',
    vHWComputerSystem.[Total Physical Memory (Bytes)] / 1048576 as 'RAM (MB)',
    vComputer.[User] as 'Primary Owner'
from vComputer
left outer join ResourceAssociation as loc
    on (
            loc.ChildResourceGuid = vComputer.Guid
            or loc.ParentResourceGuid = vComputer.Guid
            )
        and loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'
left outer join vItem as I
    on loc.ChildResourceGuid = I.Guid
left outer join vHWProcessor
    on vComputer.Guid = vHWProcessor._ResourceGuid
left outer join vHWComputerSystem
    on vComputer.Guid = vHWComputerSystem._ResourceGuid
join CollectionMembership
    on CollectionMembership.ResourceGuid = vcomputer.Guid
join vCollection
    on vCollection.Guid = CollectionMembership.CollectionGuid
where (vComputer.IsManaged = 1)
    and (vComputer.[OS Name] not like '%Server%')
    and vCollection.name = 'All Computers without SEP'
order by 'Computer Name'

Dnoah beat me to it but I'll post mine anyway in case the format is easier for you to follow.

SELECT DISTINCT  

      vComputer.Guid,

      vComputer.Name AS 'Computer Name',

      vComputer.[IP Address],

      vComputer.[OS Name],

      vComputer.[OS Revision],

      vComputer.[System Type],

      vHWComputerSystem.Manufacturer,

      vHWComputerSystem.Model,

      vHWComputerSystem.[Identifying Number] AS 'Serial Number',

      vHWProcessor.Model AS 'Processor',

      vHWComputerSystem.[Total Physical Memory (Bytes)] / 1048576 AS 'RAM (MB)',

      vComputer.[User] AS 'Primary Owner',

      vCollection.Name

 FROM vComputer

      LEFT OUTER JOIN ResourceAssociation AS loc ON (loc.ChildResourceGuid = vComputer.Guid

                  OR loc.ParentResourceGuid = vComputer.Guid)

                  AND loc.ResourceAssociationTypeGuid = '05DE450F-39EA-4AAE-8C5F-77817889C27C'

      LEFT OUTER JOIN vItem AS I ON loc.ChildResourceGuid = I.Guid

      LEFT OUTER JOIN vHWProcessor ON vComputer.Guid = vHWProcessor._ResourceGuid

      LEFT OUTER JOIN vHWComputerSystem ON vComputer.Guid = vHWComputerSystem._ResourceGuid

      JOIN CollectionMembership ON CollectionMembership.ResourceGuid = vComputer.Guid

      JOIN vCollection ON vCollection.Guid = CollectionMembership.CollectionGuid

 WHERE   

      vComputer.IsManaged = 1

      AND vComputer.[OS Name] NOT LIKE '%Server%'

      AND vCollection.Name = '.All Computers without SEP'

 ORDER BY 'Computer Name'