SQL join to find inconsistencies between two data sources

Question

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.

Thanks

Answer 1

Try the following:

SELECT displayName, 'report_1' as type
FROM report_1 r1 
LEFT OUTER JOIN report_2 r2 ON r1.SAMAccountName = r2.SAMAccountName
WHERE r2.SAMAccountName IS NULL
UNION
SELECT displayName, 'report_2' as type
FROM report_1 r1
RIGHT OUTER JOIN report_2 r2 ON r1.SAMAccountName = r2.SAMAccountName
WHERE r1.SAMAccountName IS NULL

Answer 2

You need to look at the EXCEPT clause. It's new to SQL SERVER 2005 and does the same thing that Oracle's MINUS does.

SQL1 EXCEPT SQL2

will give you all the rows in SQL1 not found in SQL2 IF

SQL1 = A, B, C, D SQL2 = B, C, E

the result is A, D

Answer 3

Building on Gabriel1836's answer, made simpler, but perhaps a bit harder to interpret:

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

Answer 4

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

Answer 5

take a look at the tabeldiff.exe that comes with sql server.