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

Join tables and exclude records...

Hi

I am trying to write a SQL query to run against 2 Oracle tables - tblNames and tblAbsence.

Let's say the tables look like this:

tblNames:

Andrew
David
John
Michael

and tblAbsence:

Andrew 01/01/05 Sick
Andrew 01/02/05 Sick
David 01/07/05 Doctor's appointment

What I need to do is to create a report that lists all of the absences from tblAbsence, plus a row for anyone in the tblNames table with no date and "In Office" in the third column if they don't have an entry in tblAbsence.

To use the above values, what I should have in my resultset is:

Andrew 01/01/05 Sick
Andrew 01/02/05 Sick
David 01/07/05 Doctor's appointment
John Null In Office
Michael Null In Office

I'm not sure how to do this. I did try a UNION of two queries, but the nearest I have been able to get is to reproduce the entries in the tblAbsence table plus all of the records in tblNames. I need to exclude the names from tblNames if they have an entry in tblAbsence.

Any ideas?

Thanks

MichaelDifferent versions of Oracle have different levels of support for standard SQL, but the way that I'd do it would be:SELECT n.Name
, a.date, Coalesce(a.comment, 'In Office)
FROM tblNames AS n
LEFT OUTER JOIN tblAbsence AS a
ON (a.name = n.name)-PatP|||Another suggestion:SELECT n.name, a.dtm, NVL(a.status, 'In office') status
FROM tblNames n, tblAbsence a
WHERE n.name = a.name (+);|||Another suggestion:SELECT n.name, a.dtm, NVL(a.status, 'In office') status
FROM tblNames n, tblAbsence a
WHERE n.name = a.name (+);Good point, but if the poster is just starting out I think it would be better to start them with standard SQL and only use eingine specific features if they are required. The closer we can keep the new users to standards, the less likely they are to get hurt by the odd quirks that we've come to know and love!

-PatP|||I agree, Pat ... however, Robojan said it is about Oracle tables so I guess this Oracle specific code won't hurt much :)|||Thanks guys - this helped me to write the query. I simplified it greatly for this (removing joins, etc.), and using the joins correctly fixed it for me.

Thanks