SQL Comparison code

JAMES125
JAMES125 used Ask the Experts™
on
I'm trying to use the following SQL code to compare 2 tables with the same data and structure.  One table labeld "current" and one labeld "old".  So far the only problem is, if field value is null in both tables it returns those records.  I would like to prevent this.  Is that possible?

SELECT "CURRENT" AS Source, t1.*
FROM CURRENT t1 LEFT JOIN
     OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE t2.[MANUFACTURER NAME] Is Null
UNION ALL SELECT "OLD" AS Source, t2.*
FROM CURRENT t1 RIGHT JOIN
      OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE t1.[MANUFACTURER NAME] Is Null;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
you could check for not null as follows:

SELECT "CURRENT" AS Source, t1.*
FROM CURRENT t1 LEFT JOIN
     OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE t2.[MANUFACTURER NAME] Is Null
    and t1.[MANUFACTURER NAME] Is not Null
UNION ALL SELECT "OLD" AS Source, t2.*
FROM CURRENT t1 RIGHT JOIN
      OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE t1.[MANUFACTURER NAME] Is Null
    and t2.[MANUFACTURER NAME] Is not Null

Author

Commented:
But if one record is null and the other isn't I want to see that difference.  Is there any way to do this?

Commented:
actually i only suggested the addition of the and's because you said you were getting extraneous null record(s).  those and's should not be required at all.

would you please post a sample of the returned results using your original query?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
This is the result with the original code.  Notice that serial number 0008390147022798 is listed twice, both with null values under MANUFACTURER NAME.  This is the same so why would it return this record?

Source      MANUFACTURER NAME      SERIAL NUMBER
CURRENT                                 0008390147022798
CURRENT      Test                            Test
OLD                                 0008390147022798
OLD      U.S. ROBOTIC      0008390363112209

Author

Commented:
Sorry about the format issue, attached is a printscreen of what I see.
Print-Screen.gif

Commented:
it looks like the manufacturer name for sn 0008390147022798 is not null, but rather blank or an empty string.  remember an outer join will return all the records from the ouer table that are not excluded by the where.  try changing your wheres to something like:

WHERE ltrim(rtrim(isnull(t2.[MANUFACTURER NAME], ''))) = ''

Author

Commented:
I apologize for my ignorance, I'm not fimiliar with SQL what exactly should I replace the Where statement with?

Commented:
here is the whole query, without the trims.  i think this will work for you:

SELECT "CURRENT" AS Source, t1.*
FROM CURRENT t1 LEFT JOIN
     OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE isnull(t2.[MANUFACTURER NAME], '') = ''
UNION ALL SELECT "OLD" AS Source, t2.*
FROM CURRENT t1 RIGHT JOIN
      OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE isnull(t1.[MANUFACTURER NAME], '') = ''

Author

Commented:
I get a wrong number of Arguments error when I tried it.  I copied it exactly as you have it written?

Author

Commented:
Here is the exact error:

Wrong number of arguments used with function in query expression 'isnull(t2.[MANUFACTURER NAME], '') = ''.
SharathData Engineer

Commented:
Can you post some sample data from both the tables and the expected output?

Commented:
sorry, you're using access, my bad - the isnull function is different in access.  (what i gave is standard sql)

try this... it will handle blank values and null values in the manu name fields.  (though now is suspect you have no null values.)

SELECT "CURRENT" AS Source, t1.*
FROM CURRENT t1 LEFT JOIN
     OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE iif(isnull(t2.[MANUFACTURER NAME], '', t2.[MANUFACTURER NAME])) = ''
UNION ALL SELECT "OLD" AS Source, t2.*
FROM CURRENT t1 RIGHT JOIN
      OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE iif(isnull(t2.[MANUFACTURER NAME], '', t2.[MANUFACTURER NAME])) = ''

Commented:
drat!  forgot to change the table names...

SELECT "CURRENT" AS Source, t1.*
FROM CURRENT t1 LEFT JOIN
     OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE iif(isnull(t2.[MANUFACTURER NAME], '', t2.[MANUFACTURER NAME])) = ''
UNION ALL SELECT "OLD" AS Source, t2.*
FROM CURRENT t1 RIGHT JOIN
      OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE iif(isnull(t1.[MANUFACTURER NAME], '', t2.[MANUFACTURER NAME])) = ''
Commented:
arrrggghhhhh.... left a t2 that should be t1... sorry i think this gets it now :-)

SELECT "CURRENT" AS Source, t1.*
FROM CURRENT t1 LEFT JOIN
     OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE iif(isnull(t2.[MANUFACTURER NAME], '', t2.[MANUFACTURER NAME])) = ''
UNION ALL SELECT "OLD" AS Source, t2.*
FROM CURRENT t1 RIGHT JOIN
      OLD t2 ON t1.[MANUFACTURER NAME] = t2.[MANUFACTURER NAME]
WHERE iif(isnull(t1.[MANUFACTURER NAME], '', t1.[MANUFACTURER NAME])) = ''

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial