SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Linking 3 tables causing duplicates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mshsilver
Posting Yak Master

110 Posts

Posted - 10/25/2013 :  12:38:40  Show Profile  Reply with Quote
Hi,
Could someone help me with query please? I have written it and it gets results but it is creating duplicate rows for some customers when there should only be one row.

I am linking an entity table (customers) to a sub entity table (sales) by going through a link table (links) and I am struggling to get my head round the logic of the results.

There is only ever one row per entity and one row per sub entity (sales) and a single (links) row per sale.

A customer could of course have several sales, therefore several (sales) rows but each with a unique sales uniqueid in the sales table and the same uniqueid for each sale in the link table, but only ever one link row per sale.

Here is the query I am running and a few results below to demonstrate the issue. It will probably be obvious to someone with a good knowledge of SQL. Clearly mine is not so good!

SELECT customers.contact, sales.uniqueid
FROM sales JOIN links ON sales.UNIQUEID = wce_linkto.Luniqueid join customers ON links.LEntityID = customers.UNIQUEID


contact sales_uniqueid
Alex Usharovsky -ZZPiaoLfSua
Alex Usharovsky -ZZPiaoLfSua
Alex Usharovsky -ZZPiaoLfSua
Alex Usharovsky -ZZPiaoLfSua
Malcolm Fairs ZuoUOatOiTac
Malcolm Fairs ZuoUOatOiTac
Malcolm Fairs ZuoUOatOiTac
Malcolm Fairs ZuoUOatOiTac
Kevin Fowler ZtS1caOP.6va
Kevin Fowler ZtS1caOP.6va
Kevin Fowler ZtS1caOP.6va
Kevin Fowler ZtS1caOP.6va

djj55
Constraint Violating Yak Guru

USA
329 Posts

Posted - 10/25/2013 :  13:10:27  Show Profile  Reply with Quote
If you use DISTINCT it will remove the duplicates. This is caused by there being a one to many join. I am sure someone else can explain it better.

djj
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/25/2013 :  13:36:47  Show Profile  Reply with Quote
quote:
Originally posted by djj55

If you use DISTINCT it will remove the duplicates. This is caused by there being a one to many join. I am sure someone else can explain it better.

djj


Don't use DISTINCT to hide an underlying problem. Of course there are valid times to use DISTINCT but way too often people slap that in there when they don't know why they are getting dupes to begin with. That introduces inefficient queries at best and bad results at the worst.

mshsilver, if you list all the related tables as well as which columns correlate to which columns we can give your the right query.
Hard for me to tell but it looks like you have 4 tables involved
(Customers, Sales, wce_linkto, links) ?

Be One with the Optimizer
TG
Go to Top of Page

mshsilver
Posting Yak Master

110 Posts

Posted - 10/25/2013 :  13:57:48  Show Profile  Reply with Quote
Thank you for the replies. I did try the distinct but i know what you are saying TG, i would rather get to the route of the issue and understand the reason for the duplicates.

Sorry, my previous query was wrong. Here it is again.

SELECT customers.contact, sales.uniqueid, sales.saletype
FROM sales JOIN links ON sales.UNIQUEID = links.Luniqueid join customers ON links.LEntityID = customers.UNIQUEID

I want to display a few fields from the customers table but to keep it simple here are the tables with the fields and their relevance.

Customers table – only ever one row per customer

uniqueid
contact

Links table – 1 row per sale

Lentityid – stores the uniqueid per sale of the entity table (customers)
luniqueid– stores the uniqueid per sale of the sub entity table (sales)

Sales table - 1 row per sale

uniqueid
saletype
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/25/2013 :  14:25:27  Show Profile  Reply with Quote
Can one sale be linked to more than one customer? Is that why you have an association table (links) rather than a [LentityID] in Sales table?

That query looks reasonable based on what you've said. It returns the same sales.uniqueid for multiple rows?
If so what I would do is check each table for dupes. So take values from ONE LINE that is a duplicate make sure for those specific values there is:
- only one row in customers for that [uniqueid] (select * from customers where uniqueid = <customerid>)
- only one row in sales for that [uniqueid] (select * from sales where uniqueid = <salesid>)
- only one row in links for that combination of [luniqueid] and [lentityid] select * from links where lentityid = <customerid> and luniqueid = <salesid>)




Be One with the Optimizer
TG
Go to Top of Page

mshsilver
Posting Yak Master

110 Posts

Posted - 10/25/2013 :  14:43:39  Show Profile  Reply with Quote
Thank you! This database is not my design and the application is not meant to put duplicate rows in any of the tables. after checking the links table with your query suggestion there is duplicates of the sale in there!

I should have checked that but assumed it was not possible... Assumption is the mother of all...

Thanks for taking your time to look.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/25/2013 :  14:53:47  Show Profile  Reply with Quote
You're welcome.
The designer of the database should have put appropriate constraints on the tables to prevent duplicates and orphans. That way those monkeys in application development can't introduce bad data from the application code I should know I'm one of the monkeys.

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000