Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mshsilver
Posting Yak Master
112 Posts |
 Posted - 2013-10-25 : 12:38:40
|
| 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.UNIQUEIDcontact sales_uniqueidAlex Usharovsky -ZZPiaoLfSuaAlex Usharovsky -ZZPiaoLfSuaAlex Usharovsky -ZZPiaoLfSuaAlex Usharovsky -ZZPiaoLfSuaMalcolm Fairs ZuoUOatOiTacMalcolm Fairs ZuoUOatOiTacMalcolm Fairs ZuoUOatOiTacMalcolm Fairs ZuoUOatOiTacKevin Fowler ZtS1caOP.6vaKevin Fowler ZtS1caOP.6vaKevin Fowler ZtS1caOP.6vaKevin Fowler ZtS1caOP.6va |
|
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-10-25 : 13:10:27
|
| 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 |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-25 : 13:36:47
|
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 OptimizerTG |
|
|
|
mshsilver
Posting Yak Master
112 Posts |
Posted - 2013-10-25 : 13:57:48
|
| 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.UNIQUEIDI 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 contactLinks 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 |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-25 : 14:25:27
|
| 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 OptimizerTG |
|
|
|
mshsilver
Posting Yak Master
112 Posts |
Posted - 2013-10-25 : 14:43:39
|
| 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. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-25 : 14:53:47
|
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 OptimizerTG |
|
|
|
|
|
|
|
|
Linking 3 tables causing duplicates