Geek asks and answers

SQL to find inconsistencies between two data sources

I have an SQL call that destroys my brain. I am trying to reconcile two application license reports.

The first report is an access database table. My predecessors were created and maintained manually. Whenever they installed or uninstalled the application, they manually updated the table, more or less. It contains many columns of inconsistent data, including the name (displayName) Network ID (SAMAccountName) and the computer name. Each entry is relevant to at least one of these fields. However, most have only 1 or 2 values.

The second report is based on an SMS inventory. It has three columns: NetbiosName for the computer name, SAMAccountName, and displayName. Each entry is named NetbiosName, but there are several zeros in SAMAccountName and displayName.

I imported both of these tables into the MS SQL Server 2005 database.

I need to report on each entry in the Access table that is not in the SMS table, and vice versa. I think that this can be done with a properly formed join and a where clause, but I don’t see how to do it.

Edit to add more details: If the records match at least one of the three columns, this is a match. Therefore, I need entries from the Access table, in which there are no names Name, NetworkID and ComputerName from the SMS table. I can do this for any one column, but I don’t see how to combine all three columns.

thank

+3
5

Kaboing , :

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

,

+3

EXCEPT. SQL SERVER 2005 , Oracle MINUS.

SQL1 SQL2

SQL1, SQL2 IF

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

A, D

+1

Based on Gabriel1836, the answer is simple, but perhaps a little more difficult 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
+1
source

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

+1
source

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
0
source

More articles:


All Articles