Query for equal records and nulls from one table

[iamtrying]

I have two tables linked on SSN. In the "FirstNoticeInjury" table, there are records which do not reside in the "SupvrRprt". How do I query for all of the records which are equal in both tables and the records not in the "SupvrRprt" table?

SELECT "FirstNoticeInjury"."SSN", "SupvrRprt"."SSN", "SupvrRprt"."LName", "SupvrRprt"."FName", "SupvrRprt"."MI", "SupvrRprt"."dateOfAccident", "SupvrRprt"."dateofInjury", "SupvrRprt"."returnToWorkDate", "SupvrRprt"."division", "SupvrRprt"."disabilityBeganDate", "SupvrRprt"."secondDisBeganDate", "SupvrRprt"."rtwFirstDisDate", "SupvrRprt"."rtwSecondDisDate", "SupvrRprt"."workStatus", "SupvrRprt"."preparedBy", "SupvrRprt"."preparedByDate", "SupvrRprt"."disabilityHED", "SupvrRprt"."disabilityHED2", "SupvrRprt"."disabilityHED3", "SupvrRprt"."comments"
FROM "WrkComp"."dbo"."SupvrRprt" "SupvrRprt" LEFT JOIN "WrkComp"."dbo"."FirstNoticeInjury" "FirstNoticeInjury" ON "FirstNoticeInjury"."SSN"<> "SupvrRprt"."SSN" WHERE "FirstNoticeInjury"."SSN" IS NULL ORDER BY "FirstNoticeInjury"."SSN"


Thanx

 

[bborissov]

Just switch main table and joined one and remove WHERE clause:

SELECT ....
      FROM FirstNoticeInjury
LEFT JOIN SupvrRprt
ORDER BY FirstNoticeInjury.SSN

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.