One last Join Question - 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

One last Join Question

Author

Topic

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-08 : 13:46:21

I was helped (or given) this code from a user on this board :-)

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
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 '______________')
ORDER BY [Computer Name],PhysicalOffice,Title

It works perfect!!

But I need to add 1 more piece of data.

I need to find all machines that are NOT obsolete which is this code.

SELECT Obsolete0
FROM v_R_System

How do I marry these two sets of code?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 13:53:32

[code]
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
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 '______________')
AND rs.Name0 NOT IN (SELECT Obsolete0
FROM v_R_System)
ORDER BY [Computer Name],PhysicalOffice,Title
[/code]

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

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-08 : 13:55:46

I ran that and it returns 0 records :-(

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 14:11:52

Try making the change shown in red

WHERE (rs.Name0 LIKE '______________')
AND rs.Name0 NOT IN (SELECT Obsolete0
FROM v_R_System WHERE Obsolete0 IS NOT NULL)
ORDER BY [Computer Name],PhysicalOffice,Title

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-08 : 14:20:51

Interesting error when I do that.

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '09700-TS-FDT01' to data type int.

thousands of our workstations do begin with numbers so it seems to choke on that.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 14:28:48

What is in the column Obsolete0? I thought it might have been the names of the systems that are obsolete. It looks like that is not it. Is it a flag that indicates whether something is obsolete? These queries might give you some clues to what they contain:

SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'v_R_System' AND COLUMN_NAME IN( 'Obsolete0',Name0));
SELECT TOP 10 Name0 FROM v_R_System;
SELECT TOP 10 Obsolete0 FROM v_R_System;

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-08 : 14:49:26

I could not run the first line. So I ran this:

SELECT TOP 10 Name0 FROM v_R_System;
SELECT TOP 10 Obsolete0 FROM v_R_System;

it returns 10 machines with obsolete=0

Obsolete is a field for SCCM client machines. If it is 0 then it is a healthy SCCM client if it is 1 then it is a bad record and then of course obsolete

It is in the view v_R_System too, I double checked that.

and my manager just added more stuff.....after I told him I don't really know SQL :-(

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-08 : 15:04:19

Assuming 0 represents non-obsolete and 1 represents obsolete, you can simply add another condition to your where clause like this:

WHERE (rs.Name0 LIKE '______________')
AND rs.Obsolete0 = 0 -- To get all non-obsolete machines
ORDER BY [Computer Name],PhysicalOffice,Title


WHERE (rs.Name0 LIKE '______________')
AND rs.Obsolete0 = 1 -- To get all obsolete machines
ORDER BY [Computer Name],PhysicalOffice,Title

The stuff you are trying to do are relatively simple and can be picked up if you just browse through some of the introductory tutorials. Some of the following are good sites:
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-08 : 15:51:52

Relatively simple? once you've done it for a few years..... ;-) Ok, one last question and then I'll shutup.

How do I merge these two?

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
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

and

select Model0 FROM v_GS_COMPUTER_SYSTEM

I need to add an Obsolete Column and have the MODEL0 show up next to the OS

I'd love to spend weeks learning this but I was told to have this written by tomorrow and I don't know SQL so I really thank everyone for your help, you have taught me a ton in a short time :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 15:58:15

just include them in the select


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,
rs.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

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

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-08 : 16:04:25

that gives an error

Msg 207, Level 16, State 1, Line 7
Invalid column name 'Model0'.

The Model0 is from a different view called FROM v_GS_COMPUTER_SYSTEM and is not in v_R_System so I'm guessing that's why?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 16:14:22

quote:
Originally posted by mqh7

that gives an error

Msg 207, Level 16, State 1, Line 7
Invalid column name 'Model0'.

The Model0 is from a different view called FROM v_GS_COMPUTER_SYSTEM and is not in v_R_System so I'm guessing that's why?

yep...i thought its in v_R_System as you didnt specify it

then use cs.Model0 instead of rs.Model0

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

mqh7
Yak Posting Veteran

58 Posts

Posted - 2012-08-08 : 16:23:29

Awesome, thank you visakh16, you are a good egg. Now that I have this I can use to to learn what it is "really" doing !!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 16:33:29

welcome
glad that i could be of help

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources