SQL join to find inconsistencies between two data sources

I have a SQL challenge that is wracking my brain. I am trying to reconcile two reports for licenses of an application.

The first report is an access database table. It has been created and maintained by hand by my predecessors. Whenever they installed or uninstalled the application they would manually update the table, more or less. It has a variety of columns of inconsistent data, including Name(displayName) Network ID(SAMAccountName) and Computer Name. Each record has a value for at least one of these fields. Most only have 1 or 2 of the values, though.

The second report is based on an SMS inventory. It has three columns: NetbiosName for the computer name, SAMAccountName and displayName. Every record has a NetbiosName, but there are some nulls in SAMAccountName and displayName.

I have imported both of these as tables in a MS SQL Server 2005 database.

What I need to do is get a report of each record in the Access table that is not in the SMS table and vice versa. I think it can be done with a properly formed join and where clause, but I can't see how to do it.

Edit to add more detail: If the records match for at least one of the three columns, it is a match. So I need the records form the Access table where the Name, NetworkID and ComputerName are all missing from the SMS table. I can do it for any one column, but I can't see how to combine all three columns.



Taking Kaboing's answer and the edited question, the solution seems to be:

FROM report_1 r1 
FULL OUTER JOIN report_2 r2 
    ON r1.SAMAccountName = r2.SAMAccountName
    OR r1.NetbiosName = r2.NetbiosName
    OR r1.DisplayName = r2.DisplayName
WHERE r2.NetbiosName IS NULL OR r1.NetbiosName IS NULL

Not sure whether records will show up multiple times

Need Your Help

std::initializer_list and reference types

c++ c++11 initializer-list

Can a std::initializer_list contain reference types (both rvalue and lvalue)? Or does one have to use pointers or a reference wrapper (such as std::ref)?

Is it appropriate to wrap each navigation element in a div?

html css html5 semantic-markup

I'm learning HTML + CSS and working on a website where I need to have a vertical navigation bar on the left side which will have four elements which can be interacted with. Is it standard practice to

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.