SQL Selection with bad data

Hi,

I have been asked to work with a query that gets a series of results which works quite well other than one table has has records with the same branch code, but a different branch text information.

for instance
                 
65510000        5550000       Dave               Wildes      273      
65510000        5550000       David              Wildes      273      
65510000        5550001       Dave               Wildes      273      

The above section of results the first two records are actually the same, just someone has managed to enter the information in twice with a different way of providing the text, and the third result is correct because of the second column.

What I am after is how to get just two records back but am a little stumped at the moment so any and all help would be appreciated.

The SQL I am using to generate this result is

WITH CTE AS (
SELECT DISTINCT
  
  AF.DLR_CD				AS [Branch]				,
  AF.DLR_NM				AS [BranchText]			,		
  AF.DLR_CITY			AS [BranchTown]			,
  AF.DLR_POST_CD		AS [BranchPostalCode]
FROM tableName_af  AF
LEFT JOIN tableName_bm  BM ON BM.BRANCH_ACC_CD = SUBSTRING(AF.DLR_CD, 2, LEN(AF.DLR_CD)) -- JOINING ON A SUBSTRING DUE TO BM AND BN TABLES DO NOT HAVE THE PREFIXED NUMBER 
LEFT JOIN tableName_bn BN ON BM.BRANCH_ACC_CD = BN.BRANCH_ACC_CD AND 
							 BM.PRNCPL_ACC_CD = BN.PRNCPL_ACC_CD
WHERE 
BM.PRNCPL_ACC_CD = @MainDealer),

Open in new window


I have disguised the actual data, but this is an accurate representation of the data.

If i remove branch text from this query it looks fine, however i need branch text as part of the results.

Any and all help appreciated
LVL 1
SimonPrice3376Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Your given tabular data looks like a violation of 2NF. Thus either your model is flawed or your way you retrieve the data. As you have decided to post an incomplete and imprecise example I need to guess:

Your JOIN condition using SUBSTRING() is incorrect.

A concise and complete example contains table DDL of the involved tables - preferably as table variables - and sample data INSERT statements as runnable T-SQL script. A complete query of your query showing the wrong data. And a tabular (ASCII) view of your desired result.
0
Pawan KumarDatabase ExpertCommented:
I dont think there is any logic which will fit in this scenario. You have to find the culprit records from the table and delete them and handle your UI and DB ( procs ) in such a manner that it will not happen again.

With query it is not possible as we dont khow which row needs to be picked and which one is to be left.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SimonPrice3376Author Commented:
I thought that was going to be the case, thank you
0
ste5anSenior DeveloperCommented:
I strongly disagree, Pawan..Cause when you can enter data like that, then the problem is not the data, but the model violating 2NF.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.