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