Video Screencast Help
Give us your opinion and win with Symantec! Please help us by taking this survey to tell us about your experience with Symantec Connect, so that we can continue to grow and improve.  Take the survey.

adjust query with another join

Created: 04 Nov 2013 • Updated: 05 Nov 2013 | 2 comments
This issue has been solved. See solution.

Hi,

I need to add another join to the below query. When I look at the below query as is it looks like there is one too many joins. Can someone help?

SELECT vComputer.Name AS 'Computer Name', vComputer.[User] AS 'Primary Owner', vComputer.[OS Name], 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 Laptops'

ORDER BY [Computer Name]

The query actually works, even with what appears to be another unneeded join. I need to add in one for INNER JOIN  vHWComputerSystem ON vComputer.Guid = vHWComputerSystem._ResourceGuid.

Should be easy enough. Just puzzled about the other join

Operating Systems:

Comments 2 CommentsJump to latest comment

Briandr88's picture

This is what I think it would look like if I follow original example:

SELECT vComputer.Name AS 'Computer Name', vComputer.[User] AS 'Primary Owner', vComputer.[OS Name], vCollection.Description,
vHWComputerSystem.Model, vHWComputerSystem.[Identifying Number] AS 'Serial Number'

FROM CollectionMembership INNER JOIN

vComputer ON CollectionMembership.ResourceGuid = vComputer.Guid INNER JOIN

vComputer ON vHWComputerSystem._ResourceGuid = vComputer.Guid INNER JOIN

INNER JOIN

vCollection INNER JOIN

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

where vCollection.Name = 'ALL Laptops'

ORDER BY [Computer Name]

Can somone point out the error?

kpjernigan's picture

Try this.

----------------------------------------------------------------------------------------------------------

SELECT 
vc.Name AS 'Computer Name'
,vc.[User] AS 'Primary Owner'
,vc.[OS Name]
,vcol.[Description]
,vhw.Model
,vhw.[Identifying Number] AS 'Serial Number'
 
FROM CollectionMembership cm 
INNER JOIN vComputer vc ON cm.ResourceGuid = vc.Guid 
INNER JOIN vHWComputerSystem vhw ON vhw._ResourceGuid = vc.Guid 
INNER JOIN vCollection vcol ON vcol.Guid = cm.CollectionGuid
 
WHERE vcol.Name IN ('ALL Laptops')
 
ORDER BY [Computer Name]
 

----------------------------------------------------------------------------------------------------------

 

-Kev

SOLUTION