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 an 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 anyone column, but I can't see how to combine all three columns.

Answers


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

SELECT *
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

How do I enforce data integrity rules in my database?

sql sql-server database database-design

I'm designing this collection of classes and abstract (MustInherit) classes…

Store everything in MongoDB or not? Use SQL alongside? (Eventual consistency)

sql-server mongodb eventual-consistency nosql

Me and my colleges are having second thoughts on using MongoDB to store al the data for our application. Some think that because of Mongo's eventual consistency when user register or updates his pr...