December 15, 2020 at 8:06 pm
There is probably a name/term for this problem and I'm just unaware of what it is. I know how to find duplicate data in a singular table but not when the data your comparing is stored in multiple tables, 2 tables with a parent child relationship.
I Restored a copy of the AdventureWorks DB but its been a long time since I used it so I deconstructed one of the views in it (vStoreWithAddresses) to use as my example. The below will return 2 rows for All Cycle Shop. These aren't duplicates because the AdressType FROM the PERSON.ADDRESSTYPE table is different and AddressLine1 from the PERSON.ADDRESSTYPE table is different but let assume in this example that they were the same, that both rows contained the values “Shipping” for the AdressType and “8713 Yosemite Ct.” for Adressline1 and thus I had 2 duplicate rows. If The value in either field in either table was different then they would not be duplicates but we’re assuming they are so how using T-SQL could I build a query that would look at the fields in these multiple tables and find duplicates?
Lets also assume that whatever solution we come up with must work on multiple duplicates so while in this example there are only 2 rows for All Cycle Shop we must not assume there is always just 1 duplicate.
SELECT s.[BusinessEntityID] ,
s.[Name] ,
at.[Name] AS [AddressType],
A.AddressID,
a.[AddressLine1] ,
a.[AddressLine2] ,
a.[City] ,
sp.[Name] AS [StateProvinceName] ,
a.[PostalCode] ,
cr.[Name] AS [CountryRegionName]
FROM [Sales].[Store] S Join [Person].[BusinessEntityAddress] BEA ON bea.[BusinessEntityID] = s.[BusinessEntityID]
Join [Person].[Address] a ON a.[AddressID] = bea.[AddressID]
Join [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID]
Join [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
Join [Person].[AddressType] at ON at.[AddressTypeID] = bea.[AddressTypeID]
WHERE 1 = 1
ORDER BY S.Name
Kindest Regards,
Just say No to Facebook!December 15, 2020 at 8:25 pm
SELECT s.[BusinessEntityID] ,
s.[Name] ,
at.[Name] AS [AddressType],
A.AddressID,
a.[AddressLine1] ,
a.[AddressLine2] ,
a.[City] ,
sp.[Name] AS [StateProvinceName] ,
a.[PostalCode] ,
cr.[Name] AS [CountryRegionName]
FROM [Sales].[Store] S Join [Person].[BusinessEntityAddress] BEA ON bea.[BusinessEntityID] = s.[BusinessEntityID]
Join [Person].[Address] a ON a.[AddressID] = bea.[AddressID]
Join [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID]
Join [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
Join [Person].[AddressType] at ON at.[AddressTypeID] = bea.[AddressTypeID]
join (SELECT s.[BusinessEntityID], count(1) as N
FROM [Sales].[Store] S Join [Person].[BusinessEntityAddress] BEA ON bea.[BusinessEntityID] = s.[BusinessEntityID]
Join [Person].[Address] a ON a.[AddressID] = bea.[AddressID]
Join [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID]
Join [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
Join [Person].[AddressType] at ON at.[AddressTypeID] = bea.[AddressTypeID]
GROUP BY s.[BusinessEntityID]
HAVING Count(1) > 1) x
on s.[BusinessEntityID] = x.[BusinessEntityID]
--WHERE s.[BusinessEntityID] = 1470
ORDER BY S.Name
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply