SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 getting different results		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mqh7
Yak Posting Veteran

58 Posts
Posted - 08/16/2012 :  14:24:21  Show Profile  Reply with Quote
In my SQL script I have noticed something. I am new to SQL so I've looked at other reports and tried to use the logic I found.


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

This top part is my Select section, it works.


select distinct
gs.name0 AS [PC Name],
gs.username0 AS [User Name],
sr.serialnumber0 AS [Serial Number],
dn.domain0 AS [Domain],
ad.ad_site_name0 AS [Site Name],
CASE WHEN ad.Obsolete0 = 0 then 'Healthy' WHEN ad.Obsolete0 = 1 then 'Obsolete' ELSE '' END AS [Client Health],
-- CASE WHEN ad.Obsolete0 = 0 then 'Healthy' ELSE CAST(ad.Obsolete0 AS CHAR(1)) END AS [Client Health],
-- ad.Obsolete0 AS [Obsolete],
ad.Operating_System_Name_and0 AS [OS Type],
ad.User_Account_Control0 AS [UAC Setting],
-- ma.Mac_Addresses0 AS [MAC Address],
ra.SMS_Assigned_Sites0 AS [SCCM Site],
sf.DisplayName0 AS [Software Name],
bt.DriveLetter0 AS [Bitlocker Drive]
----------------------------------------------------------------------------------------

Since I'm new to this I saw others JOIN the select data this way. gs.resourceID = all of the JOINED views.
This returns 1281 rows.

FROM v_gs_computer_system gs INNER JOIN v_GS_SYSTEM_ENCLOSURE sr ON gs.resourceID = sr.ResourceID
INNER JOIN v_GS_SYSTEM dn ON gs.resourceID = dn.resourceID
INNER JOIN v_R_System ad ON gs.ResourceID = ad.resourceID
INNER JOIN v_RA_System_SMSAssignedSites ra ON gs.resourceID = ra.ResourceID
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS sf ON gs.resourceID = sf.ResourceID
INNER JOIN v_GS_BITLOCKER bt ON gs.resourceID = bt.ResourceID


----------------------------------------------------------------------------------------
Then I saw it done this way. Instead of having gs.resourceID equal all following JOINED views this has gs.resourceID = sr.resourceID etc. So each one points to the next alias.

When I run this code below it returns 1540 rows.

FROM v_gs_computer_system gs INNER JOIN v_GS_SYSTEM_ENCLOSURE sr ON gs.resourceID = sr.ResourceID
INNER JOIN v_GS_SYSTEM dn ON sr.ResourceID = gs.resourceID
INNER JOIN v_R_System ad ON gs.ResourceID = ad.resourceID
INNER JOIN v_RA_System_SMSAssignedSites ra ON ad.ResourceID = ra.ResourceID
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS sf ON ra.ResourceID = sf.ResourceID
INNER JOIN v_GS_BITLOCKER bt ON sf.ResourceID = bt.ResourceID

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

Why would they return different results?

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 08/16/2012 :  14:50:08  Show Profile  Reply with Quote
nope. so far is its all INNER JOIN the results will be the same unless you've some varying filter conditions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Edited by - visakh16 on 08/16/2012 14:51:43
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 08/16/2012 :  14:53:53  Show Profile  Reply with Quote
ok. second query has a mistake


FROM v_gs_computer_system gs INNER JOIN v_GS_SYSTEM_ENCLOSURE sr ON gs.resourceID = sr.ResourceID 
INNER JOIN v_GS_SYSTEM dn ON sr.dn.ResourceID = gs.resourceID
INNER JOIN v_R_System ad ON gs.ResourceID = ad.resourceID
INNER JOIN v_RA_System_SMSAssignedSites ra ON ad.ResourceID = ra.ResourceID
INNER JOIN v_GS_ADD_REMOVE_PROGRAMS sf ON ra.ResourceID = sf.ResourceID
INNER JOIN v_GS_BITLOCKER bt ON sf.ResourceID = bt.ResourceID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Edited by - visakh16 on 08/16/2012 14:54:10
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
694 Posts
Posted - 08/16/2012 :  15:00:41  Show Profile  Reply with Quote
This join is not correct: INNER JOIN v_GS_SYSTEM dn ON sr.ResourceID = gs.resourceID

It is not relating the table v_GS_SYSTEM to any other table because the join definition is not referencing this table. The join condition is reference 2 other tables and returning all rows for that table when the condition is valid.

To make matters worse, you then use DISTINCT to get rid of the duplicate values - which causes a lot of additional processing to be done that isn't necessary.
Go to Top of Page

mqh7
Yak Posting Veteran

58 Posts
Posted - 08/16/2012 :  17:07:03  Show Profile  Reply with Quote
awesome, once again thank you!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47099 Posts
Posted - 08/16/2012 :  17:13:02  Show Profile  Reply with Quote
wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000