comparing two columns

[22strider]

Hello Friends

I have two tables of part numbers with two columns each (Part number and revision level). I am trying to find part numbers that have revision level conflict between two tables (join on part number).

There are some part numbers (in both tables) that have revision level NULL. I can find part numbers where revision is not same in both the tables, I can also find part numbers where revision in one table is NULL and IS NOT NULL in other.

Now, there are some part numbers for which revision level is NULL in both the columns; for some reason these part numbers keep showing in the result query. If both are NULL then for my requirement these are not at conflict. I've tried :

WHERE ((table1.revision <> table2.revision) OR (table1.revision IS NULL AND table2.revision IS NOT NULL) OR (table1.revision IS NOT NULL AND table2.revision IS NULL)) AND NOT (table1.revision IS NULL AND table2.revision IS NULL)

any ideas? It may be very simple in the end but I havn't been able to make it work.

Again the idea is to stop records with revision levels NULL in both the tables from appearing in the query results.

Thanks for your time
Rajesh

 

[jackd]

Consider 2 tables Part1 and Part2
Part1
PartNo Number
RevisionLevel Number

Part2
PartNo Number
RevisionLevel Number

Here is code that will show matching PartNo is these tables and will exclude those records where RevisionLevel IS Null in both tables

Note in the SQL I explicitly output the value IS NULL if a revisionLevel is Null

SELECT Part2.PartNo
, IIf(IsNull([Part2].[RevisionLevel]),"IS NULL",[Part2].[RevisionLevel]) AS Expr1
, Part1.PartNo
, IIf(IsNull(Part1.RevisionLevel),"IS NULL",Part1.revisionLevel) AS Expr2
FROM Part2 INNER JOIN Part1 ON Part2.PartNo = Part1.PartNo
WHERE  
Not ( isNull(Part1.revisionLevel) and isNull (part2.revisionLevel))

You may be able to adapt this as required.

You may also want to read a little on "De Morgan".

Good luck