how to join all this code? - 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 Programming

how to join all this code?

Author

Topic

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-08 : 10:30:09

sorry if this is an easy question.....

I have 3 sections of code, each works when I run it within the SQL Management Studio but I don't know how to combine all 3 together.

I stole this code from other reports that I found in our SCCM environment, I just don't know how to put it all in one set of code and to sort on TITLE.

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

// This works and will return the OS (XP or Window 7) but I need it to ONLY look at workstations. Not sure how to look at only workstations and to show platform type (x86 or x64)

Select Operating_System_Name_and0 FROM v_R_System

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

// this works fine and returns the proper information

SELECT DISTINCT
CASE WHEN ru.Department0 IS NULL then '(null)' ELSE ru.Department0 END AS Department
FROM v_R_System AS rs
LEFT OUTER JOIN v_R_User AS ru
ON rs.User_Name0 = ru.User_Name0
WHERE (rs.Name0 LIKE '0____-TC-7____')
AND (rs.Name0 NOT LIKE '00020-TC-_____')
ORDER BY
Department

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

// this works fine and returns the proper information

SELECT DISTINCT
CASE WHEN ru.Title0 IS NULL then '(null)' ELSE ru.Title0 END AS Title
FROM v_R_System AS rs
LEFT OUTER JOIN v_R_User AS ru
ON rs.User_Name0 = ru.User_Name0
WHERE (rs.Name0 LIKE '0____-TC-7____')
AND (rs.Name0 NOT LIKE '00020-TC-_____')
ORDER BY
Title

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 10:33:04

you want distinct values of both the columns title and department individually or you want distinct combination of them?>

also for first part whats the column that has information about workstations

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

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-08 : 10:40:44

I am new to SQL so bare with me. I was asked to write a report and I'm not sure how SQL works. how do I create "distinct values of both columns?

and in the DB I see v_R_System and it has Operating_System_Name_and0 but I don't see anything that says if it is x86 or x64. I think that might be in the chassis type but I'm not sure.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 10:48:05

quote:
Originally posted by mqh7

I am new to SQL so bare with me. I was asked to write a report and I'm not sure how SQL works. how do I create "distinct values of both columns?

and in the DB I see v_R_System and it has Operating_System_Name_and0 but I don't see anything that says if it is x86 or x64. I think that might be in the chassis type but I'm not sure.

if its distinct values of both then it wouldnt make sense to merge them onto single result. so i guess what you want is distinct combination which can be given as


SELECT DISTINCT CASE WHEN ru.Department0 IS NULL then '(null)' ELSE ru.Department0 END AS Department,
CASE WHEN ru.Title0 IS NULL then '(null)' ELSE ru.Title0 END AS Title
FROM v_R_System AS rs 
LEFT OUTER JOIN v_R_User AS ru 
ON rs.User_Name0 = ru.User_Name0 
WHERE (rs.Name0 LIKE '0____-TC-7____') 
AND (rs.Name0 NOT LIKE '00020-TC-_____')
ORDER BY Department,Title

for the latter part unless you tell us what column is and how values are we wont be able to suggest anything

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

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-08 : 10:53:36

very neat, that worked. and I have been sifting through all of the other SCCM reports and I found the code I need. how would I join this to your code above?

SELECT DISTINCT
dbo.v_R_System.Name0 AS [Computer Name], dbo.v_GS_OPERATING_SYSTEM.Caption0 AS OS,
dbo.v_GS_COMPUTER_SYSTEM.SystemType0 AS Architecture
FROM dbo.v_R_System LEFT OUTER JOIN
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER JOIN
dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
ORDER BY [Computer Name]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 11:05:33

which of the columns contain the workstation info? also whats the PK for v_R_System table?

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

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-08 : 11:09:53

hi, the last code I copied here has the information.

SELECT DISTINCT
dbo.v_R_System.Name0 AS [Computer Name], dbo.v_GS_OPERATING_SYSTEM.Caption0 AS OS,
dbo.v_GS_COMPUTER_SYSTEM.SystemType0 AS Architecture
FROM dbo.v_R_System LEFT OUTER JOIN
dbo.v_GS_OPERATING_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_OPERATING_SYSTEM.ResourceID LEFT OUTER JOIN
dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_R_System.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID
ORDER BY [Computer Name]

when I run this code it returns the OS and platform. I just don't know how to join this code with the code you wrote.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 11:16:25

[code]SELECT DISTINCT
rs.Name0 AS [Computer Name], os.Caption0 AS OS,
cs.SystemType0 AS Architecture,
CASE WHEN ru.Department0 IS NULL then '(null)' ELSE ru.Department0 END AS Department,
CASE WHEN ru.Title0 IS NULL then '(null)' ELSE ru.Title0 END AS Title
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 LIKE '0____-TC-7____')
AND (rs.Name0 NOT LIKE '00020-TC-_____')
ORDER BY [Computer Name],Department,Title
[/code]

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

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-08 : 11:24:45

very cool, that worked. Thank you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources