Adding field duplicates records
Adding field duplicates records
(OP)
Hi
I have a View which returns 255 rows. I used it in another table (Customer addresses) as I had to get a field out from this table.
When I create a new view and add in the table and tun without the field added I get the expected 255 rows. As soon as I add in the field udfTrexAccountManager t then brings 380 rows back, duplicating some of the contacts. I have tried various joins on the customerid but cannot get the right result. Any ideas please.
I have a View which returns 255 rows. I used it in another table (Customer addresses) as I had to get a field out from this table.
When I create a new view and add in the table and tun without the field added I get the expected 255 rows. As soon as I add in the field udfTrexAccountManager t then brings 380 rows back, duplicating some of the contacts. I have tried various joins on the customerid but cannot get the right result. Any ideas please.
CODE --> sql
SELECT DISTINCT dbo.[148-vwCRMySalesRepCodeCP].CustomerContactID, dbo.[148-vwCRMySalesRepCodeCP].CustomerCode, dbo.[148-vwCRMySalesRepCodeCP].Name AS [Customer Name], dbo.[148-vwCRMySalesRepCodeCP].[Display Name], dbo.[148-vwCRMySalesRepCodeCP].Salutation, dbo.[148-vwCRMySalesRepCodeCP].FirstName, dbo.[148-vwCRMySalesRepCodeCP].LastName, dbo.[148-vwCRMySalesRepCodeCP].JobTitle, dbo.[148-vwCRMySalesRepCodeCP].Telephone, dbo.[148-vwCRMySalesRepCodeCP].Email, dbo.[148-vwCRMySalesRepCodeCP].Mobile, dbo.[148-vwCRMySalesRepCodeCP].CustomerID AS CustID, dbo.[148-vwCRMySalesRepCodeCP].SalesRepID, dbo.[148-vwCRMySalesRepCodeCP].SalesRep, dbo.[148-vwCRMySalesRepCodeCP].CusttomerType, dbo.CustomerAddress.Deleted, dbo.[148-vwCRMySalesRepCodeCP].udfTrex, dbo.CustomerAddress.udfTrexAccountManager FROM dbo.CustomerAddress INNER JOIN dbo.[148-vwCRMySalesRepCodeCP] ON dbo.CustomerAddress.CustomerID = dbo.[148-vwCRMySalesRepCodeCP].CustomerID WHERE (dbo.CustomerAddress.Deleted <> 1) AND (dbo.[148-vwCRMySalesRepCodeCP].SalesRepID = 2205)
RE: Adding field duplicates records
Looks to me that you do, and most of the fields in CustomerAddress are the same for the same CustomerID, except udfTrexAccountManager field which makes your DISTINCT 'not working' any more.
BTW, I would use some aliases to make your statement 'shorter':
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Adding field duplicates records
Yes there can be multiple addresses for a customer. Also there could be many contacts for a customer.
Is there a way round this?
Thanks
RE: Adding field duplicates records
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Adding field duplicates records
If I keep the udfTrexAccountManager(s) out of the query I am getting the results I am expecting.
Thanks for the replies
RE: Adding field duplicates records
If there are two different values in udfTrexAccountManager that means the two records differ in that field, thus DISTINCT does keep both of them.
Chriss
RE: Adding field duplicates records
Let's say this is what you have:
How do you want to display the data for Joe Nobody?
You could do some fancy SQL and do:
CustomerID CustomerName udfTrexAccountManager ... ... ... 123 Joe Nobody James Blue, Susie Cute ... ... ...
Unless... UDF in udfTrexAccountManager stands for User Defined Function - if that's the case, just modify your Function to return 'James Blue, Susie Cute'---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Adding field duplicates records
What you show doesn't differ visually, but it must differ in one field, maybe a field you don't post yet, otherwise DISTINCT would have eliminated that row. Maybe you need to scroll right to see it.
All in all, you have more control over which fields establish one distinct record for you, if you'd switch to using GROUP BY. That has further conditions about the fields you have in your result that you don't group by., though. It's more complex than just writing DISTINCT, but you don't necessarily get what you think you should get with DISTINCT, because you don't gripe the concept, obviously, if you think that this is bound to a specific ID. No, it's not.
Besides, don't post email addresses, this is against forum policies and it also may breach the data privacy you owe to the customers you have in your database.
Chriss
RE: Adding field duplicates records
RE: Adding field duplicates records
though it's not possible for you to delete a post fully. There's still the [Post Deleted] link that will show it.
You should have red flagged and asked for deletion.
Chriss
RE: Adding field duplicates records
It's not possible for deleted posts to redflag them anymore. But you still get contact to the staff of tek-tips that can do so. You could red flag your post "Noted and deleted the post". Next step is you describe why you redflagged, and there you can mention you wanted to fully delete your earlier post and ask to fully delete it.
Though you will not get expelled from the forum for breaking a policy. Don't worry too much about it.
Chriss
RE: Adding field duplicates records
RE: Adding field duplicates records
The key word here may be: visually
Since you've got what you needed before you added udfTrexAccountManager, I would concentrate on the value in this field. I see sometimes an extra Space (or 2, or more), or a carriage return at the end of the field, or some other 'unprintable' character that you cannot see.
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Adding field duplicates records
This sentence has more and more spaces between words.
But it shows the same spacing as the sentence with the normal single space between all words:
This sentence has more and more spaces between words.
Screenshot of the post editor for proof:
So even showing your record to us you might fail to let us find the difference as copying it over or the forum remove the diffference. And that's not a bug, that's a feature.
To show what the SQL result is, it may be better to also show us a screenshot. And don't forget to scroll right if there's more. Also, even with a screenshot there are differences that can be invisible or hard to see, like a little l looks ver much like a capital I or vice versa.
Anyway it is, what causes it, the copying, the posting, the automatic forum "correction" of spacings, DISTINCT does not fail to see whether two results differ and then won't eliminate them, also it won't miss a double record. There is a difference in there that causes DISTINCT not to remove it.
Chriss
RE: Adding field duplicates records
CODE
3367 KE31 Frank Key (Nottingham) NULL NULL ian.beaver@some_domain.co.uk 3367 KE31 Frank Key (Nottingham) NULL NULL ian.beaver@some_domain.co.uk
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Adding field duplicates records
RE: Adding field duplicates records
It points out you don't have your data fully normalized. There is the possibility to have 1:1 instead of 1:n related data, which would make a second detail record fail to be added.
That two record should have the same udfTrexAccountManager but have a different one points out you store two records with it and copy the same udfTrexAccountManager value, but once the database has two records for something the chance is there to have a difference. If you really would avoid this redundancy you'd not have the problem.
Andy already pointed out the UDF in udfTrexAccountManager could mean this comes from a user defined function. Then that might cause the difference somehow. We don't have hands on the system to see what's in it. We can only guess that's from a function call, we can't even see whether that's done right with the query or it has done something to fill the column of that name.
Just a general thought: If a function copies something from an origin, then you change the origin value and you copy again into yet another copy, the two copies reflect the old and the new origin value and differ. That could cause this. Copies don't remember to be copies of something and don't change with the origin when it changes. Just like actual real carbon copies. That's why you reference anything with foreign keys in databases, that point to the origin instead of copying it. That way origin changes are always taken into account when you finally join by the foreign key and read the origin.
Chriss