Compare Data in two differant tables

Here is my query
Select Prospect.XTeleLink as exp1, TeleScrp.XteleLink as exp2
from Prospect, XTeleLink
Where exp2 <> exp1 and TeleScrp.XResultCode = 9
What I want is to find XTeleLink numbers where the XResultCode = 9 in the
TeleScrp table, but there is NOT at record in my Prospect table that has
that same XTeleLink
I have some missing data, there should always be data in the prospect table,
and I am trying to find out what is missing!
Any help in adavnce, thanx!
DennisSomething like this?
SELECT A.XTeleLink
FROM TeleScrp
WHERE XResultCode = 9
AND NOT EXISTS (SELECT * FROM Prospect B WHERE A.XTeleLink = B.XTeleLink)
Keith
"Dennis Burgess" <dmburgess@.surdyke.remvove.com> wrote in message
news:uKTvcliCFHA.3976@.tk2msftngp13.phx.gbl...
> Here is my query
> Select Prospect.XTeleLink as exp1, TeleScrp.XteleLink as exp2
> from Prospect, XTeleLink
> Where exp2 <> exp1 and TeleScrp.XResultCode = 9
> What I want is to find XTeleLink numbers where the XResultCode = 9 in the
> TeleScrp table, but there is NOT at record in my Prospect table that has
> that same XTeleLink
> I have some missing data, there should always be data in the prospect
table,
> and I am trying to find out what is missing!
> Any help in adavnce, thanx!
> Dennis
>|||Try,
select
a.xtelelink
from
telescrp as a
left join
prospect as b
on a.xtelelink = b.xtelelink
where
b.xtelelink is null
and a.xresultcode = 9
-- or
select
a.xtelelink
from
telescrp as a
where
a.xresultcode = 9
and not exists (select * from prospect as b where a.xtelelink = b.xtelelink)
AMB
"Dennis Burgess" wrote:

> Here is my query
> Select Prospect.XTeleLink as exp1, TeleScrp.XteleLink as exp2
> from Prospect, XTeleLink
> Where exp2 <> exp1 and TeleScrp.XResultCode = 9
> What I want is to find XTeleLink numbers where the XResultCode = 9 in the
> TeleScrp table, but there is NOT at record in my Prospect table that has
> that same XTeleLink
> I have some missing data, there should always be data in the prospect tabl
e,
> and I am trying to find out what is missing!
> Any help in adavnce, thanx!
> Dennis
>
>|||Untested due to lack of DDL...
SELECT P.XTeleLink, TS.XTeleLink
FROM XTeleLink TS
LEFT OUTER JOIN Prospect P ON TS.XTeleLink = P.XTeleLink
WHERE TS.XResultCode = 9
AND P.XTeleLink IS NULL
Paul
"Dennis Burgess" wrote:

> Here is my query
> Select Prospect.XTeleLink as exp1, TeleScrp.XteleLink as exp2
> from Prospect, XTeleLink
> Where exp2 <> exp1 and TeleScrp.XResultCode = 9
> What I want is to find XTeleLink numbers where the XResultCode = 9 in the
> TeleScrp table, but there is NOT at record in my Prospect table that has
> that same XTeleLink
> I have some missing data, there should always be data in the prospect tabl
e,
> and I am trying to find out what is missing!
> Any help in adavnce, thanx!
> Dennis
>
>|||Excellent, Now,
How do you also look for a field from the second Prospect Table?
Dennis
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OM$LUtiCFHA.328@.tk2msftngp13.phx.gbl...
> Something like this?
> SELECT A.XTeleLink
> FROM TeleScrp
> WHERE XResultCode = 9
> AND NOT EXISTS (SELECT * FROM Prospect B WHERE A.XTeleLink = B.XTeleLink)
> --
> Keith
>
> "Dennis Burgess" <dmburgess@.surdyke.remvove.com> wrote in message
> news:uKTvcliCFHA.3976@.tk2msftngp13.phx.gbl...
> table,
>|||Something like this?
SELECT A.XTeleLink
FROM TeleScrp
WHERE XResultCode = 9
AND NOT EXISTS (SELECT * FROM Prospect B WHERE A.XTeleLink = B.XTeleLink
/*here is the new part*/
AND A.SomeColumn = B.SomeColumn)
Keith
"Dennis Burgess" <dmburgess@.surdyke.remvove.com> wrote in message
news:uAX5d%23iCFHA.2512@.TK2MSFTNGP15.phx.gbl...
> Excellent, Now,
> How do you also look for a field from the second Prospect Table?
> Dennis
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:OM$LUtiCFHA.328@.tk2msftngp13.phx.gbl...
B.XTeleLink)
the
has
>