help with understanding SQL code

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

help with understanding SQL code

Author

Topic

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-10 : 10:58:18

The following code was given to me from a user on this forum. Now that our fire is out I've had time to look at it and I'm trying to better understand how it works. I am unclear on a few things that I was hoping someone could point out.

// this code works perfect //
SELECT DISTINCT
rs.Name0 AS [Computer Name], os.Caption0 AS OS,
cs.SystemType0 AS Architecture,
CASE WHEN ru.physicalDeliveryOfficeNam0 IS NULL then '(null)' ELSE ru.physicalDeliveryOfficeNam0 END AS PhysicalOffice,
CASE WHEN ru.Title0 IS NULL then '(null)' ELSE ru.Title0 END AS Title,
rs.Obsolete0,
cs.Model0
FROM dbo.v_R_System rs
LEFT OUTER JOIN
dbo.v_GS_OPERATING_SYSTEM os ON rs.ResourceID = os.ResourceID
LEFT OUTER JOIN
dbo.v_GS_COMPUTER_SYSTEM cs ON rs.ResourceID = cs.ResourceID
LEFT OUTER JOIN v_R_User AS ru
ON rs.User_Name0 = ru.User_Name0
WHERE (rs.Name0 NOT LIKE '')
AND rs.Obsolete0 = 0 -- To get all non-obsolete machines
ORDER BY [Computer Name],PhysicalOffice,Title

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

SELECT DISTINCT
rs.Name0 AS [Computer Name], os.Caption0 AS OS,
cs.SystemType0 AS Architecture,

I understand the SELECT but why rs.Name0? What is the rs? and why cs for systemtype? Are those made up or must they be what they are?

||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

CASE WHEN ru.physicalDeliveryOfficeNam0 IS NULL then '(null)' ELSE ru.physicalDeliveryOfficeNam0 END AS PhysicalOffice,
CASE WHEN ru.Title0 IS NULL then '(null)' ELSE ru.Title0 END AS Title,

Why do we define rs.Name0, os.Caption0 & cs.SystemType0 under SELECT but we don't do the same for ru.physicalDeliverOfficeName0 and ru.Title0? How come they are used "on the fly"?

||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

rs.Obsolete0,
cs.Model0

Again, why the rs and cs?

||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

And I'm reading up on JOINS but I still don't get the relationship of the following

FROM dbo.v_R_System rs
LEFT OUTER JOIN
dbo.v_GS_OPERATING_SYSTEM os ON rs.ResourceID = os.ResourceID
LEFT OUTER JOIN
dbo.v_GS_COMPUTER_SYSTEM cs ON rs.ResourceID = cs.ResourceID
LEFT OUTER JOIN v_R_User AS ru
ON rs.User_Name0 = ru.User_Name0

I'll look in the VIEWS but I'm guessing they share a common field?

||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 11:17:35

rs,cs etc are short names or aliases given for table. they're easier ways to refering tables rather than repeating the full table names everywhere.

i didnt understand below question. can you elaborate?
Why do we define rs.Name0, os.Caption0 & cs.SystemType0 under SELECT but we don't do the same for ru.physicalDeliverOfficeName0 and ru.Title0? How come they are used "on the fly"?

For understanding joins read about joins here

http://www.w3schools.com/sql/sql_join.asp

its as simple as trying to match values between tables based on values of columns specified by ON condition
LEFT OUTER JOIN ensures it returns every record from left table even if there's no match from right side

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

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-10 : 11:21:34

i didnt understand below question. can you elaborate?
Why do we define rs.Name0, os.Caption0 & cs.SystemType0 under SELECT but we don't do the same for ru.physicalDeliverOfficeName0 and ru.Title0? How come they are used "on the fly"?

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

Under the SELECT DISTINCT we select the following.

rs.Name0 AS [Computer Name], os.Caption0 AS OS,
cs.SystemType0 AS Architecture,

but we Don't SELECT ru.physicalDeliveryOfficeNam0 or ru.Title0. Why? How can we use them if we never selected them? OR is "everything" under SELECT and "before" FROM the whole SELECT section?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 11:27:15

quote:
Originally posted by mqh7

i didnt understand below question. can you elaborate?
Why do we define rs.Name0, os.Caption0 & cs.SystemType0 under SELECT but we don't do the same for ru.physicalDeliverOfficeName0 and ru.Title0? How come they are used "on the fly"?

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

Under the SELECT DISTINCT we select the following.

rs.Name0 AS [Computer Name], os.Caption0 AS OS,
cs.SystemType0 AS Architecture,

but we Don't SELECT ru.physicalDeliveryOfficeNam0 or ru.Title0. Why? How can we use them if we never selected them? OR is "everything" under SELECT and "before" FROM the whole SELECT section?

everything under select and before FROM is a part of SELECT list itself.

I would really suggest you starting with basics of SQL right from SELECT syntax. The query posted is one of very simple queries so its high time you learn and understand what the components of query means and how to write them as per your requirement

the link posted before has very good source of basic SQL so you should get a good start with it. As always post when you have something unclear and then people here will help you out!

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

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-10 : 11:35:36

very good, thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-10 : 11:58:17

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources