Compare values

I have two tables both with first name and last name fields. I want to creat
e
a query that pulls the names out of table ctct that are not in table ctctws.
I tried this query but does not give me the expected reults
select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
ctctws.c_first_namews
from ctct, ctctws
where c_last_name <> c_last_namews and c_first_name <> c_first_namews
Can anyone suggest away to produce the required result.
I Really appreciate any help
Thanksselect ctct.c_last_name, ctct.c_first_name
FROM ctct
WHERE NOT EXISTS(SELECT NULL FROM ctctws
WHERE c_last_name = c_last_namews and c_first_name = c_first_namews)
Jacco Schalkwijk
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks|||I suggest NOT EXISTS. Below is an untested example in which I removed the
ctctws data from the column list since these refer to non-existing rows:
SELECT
ctct.c_last_name,
ctct.c_first_name
FROM ctct
WHERE NOT EXISTS
(
SELECT *
FROM ctctws
WHERE
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
)
You could also use LEFT JOIN, but note that unlatching ctctws rows will
contain NULL values.
SELECT
ctct.c_last_name,
ctct.c_first_name,
ctctws.c_last_namews,
c_first_namews
FROM ctct
LEFT JOIN ctctws ON
ctct.c_last_name = ctctws.c_last_namews AND
ctct.c_first_name = ctctws.c_first_namews
WHERE
ctctws.c_last_namews IS NOT NULL
Both of these examples will consider NULL values as unmatching, even if the
values are NULL in both tables. You can add IS NULL criteria to the queries
if you need to consider NULLs as equal for comparison purposes.
Hope this helps.
Dan Guzman
SQL Server MVP
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>I have two tables both with first name and last name fields. I want to
>create
> a query that pulls the names out of table ctct that are not in table
> ctctws.
> I tried this query but does not give me the expected reults
> select ctct.c_last_name, ctct.c_first_name, ctctws.c_last_namews,
> ctctws.c_first_namews
> from ctct, ctctws
> where c_last_name <> c_last_namews and c_first_name <> c_first_namews
> Can anyone suggest away to produce the required result.
> I Really appreciate any help
> Thanks|||Great that works thanks!!!!! So that shows me the ones that are not there ho
w
do i find the ones that are there?
Thanks!!!
"Dan Guzman" wrote:

> I suggest NOT EXISTS. Below is an untested example in which I removed the
> ctctws data from the column list since these refer to non-existing rows:
> SELECT
> ctct.c_last_name,
> ctct.c_first_name
> FROM ctct
> WHERE NOT EXISTS
> (
> SELECT *
> FROM ctctws
> WHERE
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> )
> You could also use LEFT JOIN, but note that unlatching ctctws rows will
> contain NULL values.
> SELECT
> ctct.c_last_name,
> ctct.c_first_name,
> ctctws.c_last_namews,
> c_first_namews
> FROM ctct
> LEFT JOIN ctctws ON
> ctct.c_last_name = ctctws.c_last_namews AND
> ctct.c_first_name = ctctws.c_first_namews
> WHERE
> ctctws.c_last_namews IS NOT NULL
> Both of these examples will consider NULL values as unmatching, even if th
e
> values are NULL in both tables. You can add IS NULL criteria to the queri
es
> if you need to consider NULLs as equal for comparison purposes.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:3A9452B9-7BE2-4552-9F32-EE12630E2240@.microsoft.com...
>
>|||lol never mind i just replaced WHERE NOT EXISTS with WHERE EXISTS
"Eric" wrote:
[vbcol=seagreen]
> Great that works thanks!!!!! So that shows me the ones that are not there
how
> do i find the ones that are there?
> Thanks!!!
> "Dan Guzman" wrote:
>