How to use multiple select statements

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 use multiple select statements

Author

Topic

hh1234
Starting Member

30 Posts

Posted - 2012-03-14 : 15:53:30

I have this code which works fine.

select distinct
Name0, ResourceID, User_Domain0, User_Name0, AD_Site_Name0,

from v_R_System

where v_R_System.ResourceID NOT IN (Select v_R_System.ResourceID
from v_R_System inner join v_GS_ADD_REMOVE_PROGRAMS on v_GS_Add_Remove_Programs.ResourceID = v_r_System.ResourceID
where
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = '%Quest Secure Password Extension x64%')

I need to add this to the code:

SELECT LastConsoleUse0
From v_GS_SYSTEM_CONSOLE_USER

And then I need the LastConsoleUse0 to appear as a Column in the final output.

How do you do that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-14 : 15:57:56

How does that new bit relate to the data, meaning how would you associate each row from v_GS_SYSTEM_CONSOLE_USER to the data in your current query?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

hh1234
Starting Member

30 Posts

Posted - 2012-03-14 : 16:06:20

NAME0 is the NetBIOS name of the PC. So I want LastConsoleUse0 (the last time a user logged on) associated with NAME0. That why we can tell for each machine the last time someone logged into the PC.

I hope that is what you were asking.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-14 : 16:35:21

Are NAME0 and LastConsoleUse0 the same? I can't tell from your explanation. Or is NAME0 available in this other view?

It'll probably help us if you showed us a data example.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

hh1234
Starting Member

30 Posts

Posted - 2012-03-14 : 16:47:40

Name0 is from from v_R_System
LastConsoleUse0 is from v_GS_SYSTEM_CONSOLE_USER

so they are from totally different views.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-14 : 16:49:06

You are missing my point. In order to add this additional view, it has to relate to the data in your current query. What is the relation? How are we supposed to add the view without knowing the relation?

How about you show us the columns being returned from v_GS_SYSTEM_CONSOLE_USER?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

hh1234
Starting Member

30 Posts

Posted - 2012-03-14 : 17:00:07

I want to join these two statements

SELECT
v_GS_COMPUTER_SYSTEM.Name0,
v_GS_COMPUTER_SYSTEM.UserName0,
v_R_System.User_Name0
FROM
v_GS_COMPUTER_SYSTEM v_GS_COMPUTER_SYSTEM,
v_R_System v_R_System
WHERE
v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
ORDER BY
v_GS_COMPUTER_SYSTEM.Name0

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

select distinct
Name0, ResourceID, User_Domain0, User_Name0, AD_Site_Name0,

from v_R_System

where v_R_System.ResourceID NOT IN (Select v_R_System.ResourceID
from v_R_System inner join v_GS_ADD_REMOVE_PROGRAMS on v_GS_Add_Remove_Programs.ResourceID = v_r_System.ResourceID
where
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 = '%Quest Secure Password Extension x64%')

So I can find out all machines that do Not have the Quest software installed. I get the PC name and the last user who logged into the PC.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources