Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Adding field duplicates records

Status
Not open for further replies.
Cpreston

Cpreston

MIS
Mar 4, 2015
987
GB
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.

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)
 
Sort by date Sort by votes
Do you have multiple records in CustomerAddress table for (some) CustomerID [ponder]
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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]' [wiggle]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Chris Miller said:
What you show doesn't differ visually
Click to expand...

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
 
Upvote 0 Downvote
Andrezejek said:
I would concentrate on the value in this field. I see sometimes an extra Space (or 2, or more)
Click to expand...
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:
tektipsspacing_y6e8xj.png



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
 
Upvote 0 Downvote
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):

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

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Upvote 0 Downvote
I will check the spacing and get back, it is just some records not all so something like this could be happening
 
Upvote 0 Downvote
it is just some records not all
Click to expand...
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Andrzejek
  • Locked
  • Question
Field types question
Replies
9
Views
638
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
Adding Text to and XML Field 1
Replies
2
Views
323
grnzbra
grnzbra
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
601
Chris Miller
Chris Miller
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
732
Chris Miller
Chris Miller
eboughey1008
  • Locked
  • Question
Import columns from one table into another table with dissimilar field layout
Replies
7
Views
419
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top