CODEWITHSUNDEEP
sqlsql-server
shimonyk
shimonyk

Reputation: 219

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.

Upvotes: 3

Views: 2494

Answers (5)

Mladen Prajdic
Mladen Prajdic

Reputation: 15685

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

Upvotes: 1

devio
devio

Reputation: 37225

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

Upvotes: 3

Jonas Lincoln
Jonas Lincoln

Reputation: 9787

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

Upvotes: 1

Mark Brady
Mark Brady

Reputation:

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

Upvotes: 1

Noah Goodrich
Noah Goodrich

Reputation: 25263

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

Upvotes: 0

Related Questions