JOINS / Exclude rows

Help:
How do I constuct a queery returning all the rows from table A that do
NOT have a match in table B for a given column?
To be more specific, I am pulling a copy of the sysprcesses table. I
then want to report out the rows that ARE NOT in my permitted logins
(i.e., potiential problems)
PermittedUsers
==============
ID LoginName
-- --
01 johnsmith
02 davebarry
-- GET A SNAPSHOT OF THE ALL PROCESSESS AND STORE THEM IN THE TEMP
TABLE #processlist
Select @.@.SERVERNAME AS [SERVERNAME], GETDATE() AS [SNAPTIME], * into
#processlist From master..sysprocesses (nolock)
-- This will give me a list of all the processes running by permitted
users:
select * INTO #goodprocesses from #processlist PL INNER JOIN
PermittedUsers PU ON (PL.loginame =PU.LoginName collate
Latin1_General_CI_AS)
-- I need a query giving me the "opposite"
select * INTO #suspectprocesses from #processlist PL INNER JOIN
PermittedUsers PU ON (PL.loginame <> PU.LoginName collate
Latin1_General_CI_AS)
-- but the above is not it.
Can anyone help?
THANKS!
d.Try:
SELECT COLUMNLIST
FROM TABLE1
WHERE NOT EXISTS (SELECT * FROM TABLE2 WHERE TABLE1.COLID = TABLE2.COLID)
HTH
Jerry
<google@.dcbarry.com> wrote in message
news:1128642174.792462.153940@.g49g2000cwa.googlegroups.com...
> Help:
> How do I constuct a queery returning all the rows from table A that do
> NOT have a match in table B for a given column?
>
> To be more specific, I am pulling a copy of the sysprcesses table. I
> then want to report out the rows that ARE NOT in my permitted logins
> (i.e., potiential problems)
>
> PermittedUsers
> ==============
> ID LoginName
> -- --
> 01 johnsmith
> 02 davebarry
>
>
> -- GET A SNAPSHOT OF THE ALL PROCESSESS AND STORE THEM IN THE TEMP
> TABLE #processlist
> Select @.@.SERVERNAME AS [SERVERNAME], GETDATE() AS [SNAPTIME], * into
> #processlist From master..sysprocesses (nolock)
>
> -- This will give me a list of all the processes running by permitted
> users:
> select * INTO #goodprocesses from #processlist PL INNER JOIN
> PermittedUsers PU ON (PL.loginame =PU.LoginName collate
> Latin1_General_CI_AS)
> -- I need a query giving me the "opposite"
> select * INTO #suspectprocesses from #processlist PL INNER JOIN
> PermittedUsers PU ON (PL.loginame <> PU.LoginName collate
> Latin1_General_CI_AS)
> -- but the above is not it.
>
> Can anyone help?
>
> THANKS!
> d.
>|||Hi
SELECT <column list> FROM TableA LEFT JOIN TableB ON TableA.pk=TableB.pk
WHERE TableB.pk IS NULL
<google@.dcbarry.com> wrote in message
news:1128642174.792462.153940@.g49g2000cwa.googlegroups.com...
> Help:
> How do I constuct a queery returning all the rows from table A that do
> NOT have a match in table B for a given column?
>
> To be more specific, I am pulling a copy of the sysprcesses table. I
> then want to report out the rows that ARE NOT in my permitted logins
> (i.e., potiential problems)
>
> PermittedUsers
> ==============
> ID LoginName
> -- --
> 01 johnsmith
> 02 davebarry
>
>
> -- GET A SNAPSHOT OF THE ALL PROCESSESS AND STORE THEM IN THE TEMP
> TABLE #processlist
> Select @.@.SERVERNAME AS [SERVERNAME], GETDATE() AS [SNAPTIME], * into
> #processlist From master..sysprocesses (nolock)
>
> -- This will give me a list of all the processes running by permitted
> users:
> select * INTO #goodprocesses from #processlist PL INNER JOIN
> PermittedUsers PU ON (PL.loginame =PU.LoginName collate
> Latin1_General_CI_AS)
> -- I need a query giving me the "opposite"
> select * INTO #suspectprocesses from #processlist PL INNER JOIN
> PermittedUsers PU ON (PL.loginame <> PU.LoginName collate
> Latin1_General_CI_AS)
> -- but the above is not it.
>
> Can anyone help?
>
> THANKS!
> d.
>