getting different results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

getting different results

Author

Topic

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-16 : 14:24:21

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

52326 Posts

Posted - 2012-08-16 : 14:50:08

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-16 : 14:53:53

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/

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-08-16 : 15:00:41

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.

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-16 : 17:07:03

awesome, once again thank you!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-16 : 17:13:02

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources