Adding field duplicates records

[Cpreston]

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.

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)

 

[Andrzejek]

Do you have multiple records in CustomerAddress table for (some) CustomerID
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':
[pre]
SELECT DISTINCT
SR.CustomerContactID, SR.CustomerCode, SR.Name AS [Customer Name],
SR.[Display Name], SR.Salutation, SR.FirstName, SR.LastName,
SR.JobTitle, SR.Telephone, SR.Email, SR.Mobile,
SR.CustomerID AS CustID, SR.SalesRepID, SR.SalesRep, SR.CusttomerType,
CA.Deleted, SR.udfTrex, CA.udfTrexAccountManager
FROM dbo.CustomerAddress CA INNER JOIN
dbo.[148-vwCRMySalesRepCodeCP] SR ON CA.CustomerID = SR.CustomerID
WHERE (CA.Deleted <> 1) AND (SR.SalesRepID = 2205)
[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

Hi

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

 

[Andrzejek]

Well, you want to have udfTrexAccountManager field added to your statement, and that makes those multiple records. So, if your Customer has 2 different udfTrexAccountManager(s), what do you want to do/display?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

I dont see how I can display other than what is there to be honest thinking about it, it is not logical.

If I keep the udfTrexAccountManager(s) out of the query I am getting the results I am expecting.

Thanks for the replies

 

[Chris Miller]

In fact it is very logical.
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

 

[Andrzejek]

I am with Chriss, "it is very logical."

Let's say this is what you have:

[pre]
CustomerID CustomerName udfTrexAccountManager
... ... ...
123 Joe Nobody James Blue
123 Joe Nobody Susie Cute
... ... ...
[/pre]
How do you want to display the data for [tt]Joe Nobody[/tt]?

You could do some fancy SQL and do:

[pre]
CustomerID CustomerName udfTrexAccountManager
... ... ...
123 Joe Nobody [blue]James Blue, Susie Cute[/blue]
... ... ...
[/pre]
Unless... UDF in [highlight #FCE94F]udf[/highlight]TrexAccountManager stands for [highlight #FCE94F]U[/highlight]ser [highlight #FCE94F]D[/highlight]efined [highlight #FCE94F]F[/highlight]unction - if that's the case, just modify your Function to return '[blue]James Blue, Susie Cute[/blue]'

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Chris Miller]

Face it: DISTINCT is a keyword that looks at all fields of the result, not just the ID.

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

 

[Cpreston]

Noted and deleted the post

 

[Chris Miller]

Fine move, CPreston,

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

 

[Chris Miller]

Red flagging means clicking the red flag "Report", which is shown besides the star and "Great post!" at thee bottom of all posts.

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

 

[Cpreston]

Thanks Chris, I have red flagged it and asked them to delete it all and admitted fault by accident. Wont make the same mistake again. Regarding the SQL I will take a look at grouping and the distinct side and open a new thread if I need further assistance. Many thanks for your assistance.

 

[Andrzejek]

What you show doesn't differ visually

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

 

[Chris Miller]

I would concentrate on the value in this field. I see sometimes an extra Space (or 2, or more)

I second that, because indeed posting here double or triple spaces are turned to single spaces.

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

 

[Andrzejek]

These two records 'look' the same, but Distinct would show them separately as two records (if one e-mail would have [highlight #FCE94F]some spaces[/highlight] at the end):

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[highlight #FCE94F]  [/highlight]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Cpreston]

I will check the spacing and get back, it is just some records not all so something like this could be happening

 

[Chris Miller]

it is just some records not all

That's the nature of it, it will only affect this one customer that has similar but not totally equal field values.

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