Finding Unmatched Rows of Data 1

[mtepfer]

I have 2 tables with client information from 2 seperate sources. One table contains all of our clients we know for sure the other we know some are missing and would like to find out which ones are missing.

I would like to be able to list all the clients from Table A then match all the clients that exist in Table B or if no match leave either blank or 'N/A'

Table A would have Last Name, and SSN
Table B would have Last Name and last 4 of SSN

Table A Sample

ID Last_Name SSN
1 Jones 123456789
2 Smith 987654321
3 Davis 876458754

Table B Sample

ID Last_Name SSN
1 Jones 6789
2 Smith 4321
3 Johnson 1234

So the output would be

ID Last Name(a) SSN(a) Last Name(b) SSN(b)
1 Jones 123456789 Jones 6789
2 Smith 987654321 Smith 4321
3 Davis 876458754

I created a view but it returns only those that match based on Last name and last 4 of SSN and I need all clients from table A to be returned regardless if there is a match or not.

Also am using SQL 2005

 

[gmmastros]

In the view you created... did you have an inner join to match the 2 tables? If so... use a left join instead.

[COLOR=blue]Select[/color] TableA.Id,
       TableA.Last_Name,
       TableA.SSN,
       TableB.Last_Name,
       TableB.SSN
[COLOR=blue]From[/color]   TableA
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] TableB
         [COLOR=blue]On[/color] TableA.Last_Name = TableB.Last_Name
         And [COLOR=#FF00FF]Right[/color](TableA.SSN, 4) = TableB.SSN

-George

"the screen with the little boxes in the window." - Moron

 

[mtepfer]

That was it, thanks when I did the view it created a cross join, guess I better have some more coffee to get my brain thinking again.

 

[SQLSister]

YOu do realize that last_name and last 4 of social would not necessarily indicate a unique person?
Tom Jones in NJ could have ssn 123-45-1111 and Jenny Jones in CA could have SSN 890-78-1111
For data integrity this would be a very bad thing to rely on.

"NOTHING is more important in a database than integrity." ESquared