iamnamja
asked on
comparing tables and dealing with null value
Hi,
I have two tables, both contain similar data. table A contains today's values, and table B contains yesterday's values.
I'm trying to create a query that will compare all values in A and B, and find only the items that had changes.
only issue that i am running into is that some fields may contain null, which is keeping securities that have null on both sides to also show up. e.g.
if field1 is null on both sides
tableA.field1 = tableB.field1 results in false.
how can i go about doing this? right now, i was planning on creating two queries, one that shows all securities that match, and then from that derive a query that shows only the missing.
I have two tables, both contain similar data. table A contains today's values, and table B contains yesterday's values.
I'm trying to create a query that will compare all values in A and B, and find only the items that had changes.
only issue that i am running into is that some fields may contain null, which is keeping securities that have null on both sides to also show up. e.g.
if field1 is null on both sides
tableA.field1 = tableB.field1 results in false.
how can i go about doing this? right now, i was planning on creating two queries, one that shows all securities that match, and then from that derive a query that shows only the missing.
P.S.
Or substitute OR for the AND in my previous post if you don't want to deal with any fields that are null.
Or substitute OR for the AND in my previous post if you don't want to deal with any fields that are null.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i'm not looking to find "missing" securities, and i'm not looking for cases where both fields are showing null.
e.g.
TableA.id TableA.value TableB.id TableB.value
1 A 1 A
2 B 2
3 3
4 D 4 Z
5 E 5 E
In the case above, i want a query that will return all values of Table A that have different values than table B.
so in this case, returned result should be:
TableA.id TableA.value
2 B
4 D
The reason why id: 3 is not included is because they're in my view same. But since they're both "NULL" its not equating to be the same in sql.
I hope this clears up some things. Thanks guys.
e.g.
TableA.id TableA.value TableB.id TableB.value
1 A 1 A
2 B 2
3 3
4 D 4 Z
5 E 5 E
In the case above, i want a query that will return all values of Table A that have different values than table B.
so in this case, returned result should be:
TableA.id TableA.value
2 B
4 D
The reason why id: 3 is not included is because they're in my view same. But since they're both "NULL" its not equating to be the same in sql.
I hope this clears up some things. Thanks guys.
iamnamja,
try the queries i posted to your actual table...
better if you have more question, upload a db with the tables.
try the queries i posted to your actual table...
better if you have more question, upload a db with the tables.
ASKER
Hi Capricorn,
I do see what you mean with your queries, and i'm sorry if i didn't fully explain my situation.
The table contains multiple fields that I need to compare. 9 to be exact. So any item that has any of the 9 fields not match up should show. Going by what you have gave an example of, I would need to create 15 queries to get the result that I am expecting. I'm hoping that there will be a simpler solution.
As for uploading the database, I can't do that as this is a sensitive material that I am working on, and besides all this is based off of a linked table.
The example above I was hoping would give an idea of what the issue is. If you require additional info, please let me know.
I do see what you mean with your queries, and i'm sorry if i didn't fully explain my situation.
The table contains multiple fields that I need to compare. 9 to be exact. So any item that has any of the 9 fields not match up should show. Going by what you have gave an example of, I would need to create 15 queries to get the result that I am expecting. I'm hoping that there will be a simpler solution.
As for uploading the database, I can't do that as this is a sensitive material that I am working on, and besides all this is based off of a linked table.
The example above I was hoping would give an idea of what the issue is. If you require additional info, please let me know.
you will need only two queries
try this first
SELECT TableA.f1, TableA.f2, TableA.f3, TableA.f4, TableA.f5, TableA.f6, TableA.f7, TableA.f8, TableA.f9, TableB.f1, TableB.f2, TableB.f3, TableB.f4, TableB.f5, TableB.f6, TableB.f7, TableB.f8, TableB.f9
FROM TableA LEFT JOIN TableB ON (TableA.f9=TableB.f9) AND (TableA.f8=TableB.f8) AND (TableA.f7=TableB.f7) AND (TableA.f6=TableB.f6) AND (TableA.f5=TableB.f5) AND (TableA.f4=TableB.f4) AND (TableA.f3=TableB.f3) AND (TableA.f2=TableB.f2) AND (TableA.f1=TableB.f1)
WHERE (((TableB.f1) Is Null) AND ((TableB.f2) Is Null) AND ((TableB.f3) Is Null) AND ((TableB.f4) Is Null) AND ((TableB.f5) Is Null) AND ((TableB.f6) Is Null) AND ((TableB.f7) Is Null) AND ((TableB.f8) Is Null) AND ((TableB.f9) Is Null));
SELECT TableA.f1, TableA.f2, TableA.f3, TableA.f4, TableA.f5, TableA.f6, TableA.f7, TableA.f8, TableA.f9, TableB.f1, TableB.f2, TableB.f3, TableB.f4, TableB.f5, TableB.f6, TableB.f7, TableB.f8, TableB.f9
FROM TableA LEFT JOIN TableB ON (TableA.f9=TableB.f9) AND (TableA.f8=TableB.f8) AND (TableA.f7=TableB.f7) AND (TableA.f6=TableB.f6) AND (TableA.f5=TableB.f5) AND (TableA.f4=TableB.f4) AND (TableA.f3=TableB.f3) AND (TableA.f2=TableB.f2) AND (TableA.f1=TableB.f1)
WHERE (((TableB.f1) Is Null) AND ((TableB.f2) Is Null) AND ((TableB.f3) Is Null) AND ((TableB.f4) Is Null) AND ((TableB.f5) Is Null) AND ((TableB.f6) Is Null) AND ((TableB.f7) Is Null) AND ((TableB.f8) Is Null) AND ((TableB.f9) Is Null));
Nz(tableA.field1) = Nz(tableB.field1)
will match if broth sides are null.
Where tableA.field1 Is Null or tableB.field1 Is Null
will find the blank fields
will match if broth sides are null.
Where tableA.field1 Is Null or tableB.field1 Is Null
will find the blank fields
WHERE (tableA.field1 <> NULL AND tableB.field1 <> NULL)