SimonPrice3376
asked on
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
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
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),
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I thought that was going to be the case, thank you
I strongly disagree, Pawan..Cause when you can enter data like that, then the problem is not the data, but the model violating 2NF.
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.