Video Screencast Help
Symantec to Separate Into Two Focused, Industry-Leading Technology Companies. Learn more.

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