Unmatched records from Join

[Shawn]

I have 2 tables.
table1: ssn, name, date, status
table2: ssn, name, dept
I wanted to know if there is it possible to get all the
unmatched records from either table. I want all the ssn's
that do not have matches. Is that possible? I tried with
an right/left outer join but it returns matches and
unmatched. Thanks.

[Ray at]

SELECT [ssn],[name],[date],[status] FROM [table1] WHERE [ssn] NOT IN (SELECT
[ssn] FROM [table2])

Ray at work

"Shawn" <sfergus2@cscc.edu> wrote in message
news:469301c37bbd$00552000$a601280a@phx.gbl...

> I have 2 tables.
> table1: ssn, name, date, status
> table2: ssn, name, dept
> I wanted to know if there is it possible to get all the
> unmatched records from either table. I want all the ssn's
> that do not have matches. Is that possible? I tried with
> an right/left outer join but it returns matches and
> unmatched. Thanks.
>
>

[Nicole Calinoiu]

Shawn,

A full outer join will do this for you. e.g. (SQL Server syntax):

SELECT A.SSN AS SSN_1, B.SSN AS SSN_2
FROM
Table1 AS A
FULL OUTER JOIN Table2 AS B
ON A.SSN = B.SSN
WHERE (A.SSN IS NULL) OR (B.SSN IS NULL)

If your db platform does not support full outer joins, you'll need to UNION
two separate outer joins. e.g. (Access syntax):

SELECT A.SSN AS SSN_1, B.SSN AS SSN_2
FROM
Table1 AS A
LEFT JOIN Table2 AS B
ON A.SSN = B.SSN
WHERE B.SSN IS NULL

UNION

SELECT B.SSN AS SSN_1, A.SSN AS SSN_2
FROM
Table2 AS A
LEFT JOIN Table1 AS B
ON A.SSN = B.SSN
WHERE B.SSN IS NULL

HTH,
Nicole

"Shawn" <sfergus2@cscc.edu> wrote in message
news:469301c37bbd$00552000$a601280a@phx.gbl...

> I have 2 tables.
> table1: ssn, name, date, status
> table2: ssn, name, dept
> I wanted to know if there is it possible to get all the
> unmatched records from either table. I want all the ssn's
> that do not have matches. Is that possible? I tried with
> an right/left outer join but it returns matches and
> unmatched. Thanks.
>
>

[Madhan]

Are u sure Its working