Search code examples
sqlsql-servercomparison

MSSQL comparing 2 columns issue

I have two tables: Customers info, like id,customerName,address etc.. and RecipientOfGoods - customerId, address. The addresses from both tables differ, the address in customer table is by registration and the address in second table is delivery address. I need to add in the first table the recipient of goods address in order to compare the two type of addresses.

use dwh01
SELECT distinct
       cus.[BranchId]
      ,cus.[CustomerId]
      ,cus.[CustomerName]
      ,cus.[Street] as StreetByReg
      ,del.Street as StreetForDelivery
      ,cus.[Postalcode] as PKByReg
      ,del.Postalcode as PKForDelivery
      ,cus.[City] as CityByReg
      ,del.City as CityForDelivery
      ,cus.[PhoneNumber]
      ,cus.[EMail]
      ,cus.[IsActive]
      ,cus.[LastChangeDate]
  FROM [dwh01].[live].[DimCustomer] cus 
 join live.DimRecipientOfGoods del on del.RecipientOfGoodsid = cus.Customerid
 where cus.BranchId in('1080','1081') and ltrim(cus.CustomerId) = '99060'

Thats my query, and i do get the needed result, but i also get on second row the same info but in the columns for address from the second table i get the address from first table. The question is how to remove that useless second row appearing and why it happens like this?

Screenshot of query and result. First row is what i need, second row is useless.

Solution

  • JOIN excluding equal rows to get only customers with new addresses.

    SELECT distinct
       cus.[BranchId]
      ,cus.[CustomerId]
      ,cus.[CustomerName]
      ,cus.[Street] as StreetByReg
      ,del.Street as StreetForDelivery
      ,cus.[Postalcode] as PKByReg
      ,del.Postalcode as PKForDelivery
      ,cus.[City] as CityByReg
      ,del.City as CityForDelivery
      ,cus.[PhoneNumber]
      ,cus.[EMail]
      ,cus.[IsActive]
      ,cus.[LastChangeDate]
  FROM [dwh01].[live].[DimCustomer] cus 
  JOIN live.DimRecipientOfGoods del ON del.RecipientOfGoodsid = cus.Customerid 
     AND (cus.[Street] <> del.[Street] OR cus.[Postalcode] <> del.[Postalcode] OR cus.[City] <> del.[City]) 
  WHERE cus.BranchId in('1080','1081') and ltrim(cus.CustomerId) = '99060'