Search code examples
sqlsap-ase

SQL code that will confirm that 2 columns of a table is 'synchronized', if not flag it

I'm having a bit of trouble writing an sql for this scenario.

For example given a table (this is just to simplify and mask the actual data...), ContactInfo, it has 2 columns Address and Phone. I would like to write an sql that will confirm that given an address, the phone will always be the same for that address (hence 'synchronized') and if they are not, flag it:

enter image description here

So in this example if person Abc and Def have same address, their phone should be same as well (ie only one landline phone number in a resident) and that person Klm should be flagged as he lives in same unit but has different phone.

***The table is denormalized but that's how it is, normalizing is not an option...

This is for Sybase 12.5 ASE.

Solution

  • Well,

    You could try this

    SELECT a.*, b.*
FROM ContactInfo a
  INNER JOIN ContactInfo b on a.address = b.address
WHERE a.phone != b.phone

    Some SQL's prefer <> to !=

    This will get you any records that match address but don't match phone number. So it will show Abc & KLM and DEF and Klm (but not Abc & Def).

    You can't tell which is right here - perhaps Klm is the correct phone number and both abc and def have moved on. Or maybe Klm has a mobile phone.