Solved

SQL entries with duplicate primary keys

Posted on 2008-06-22
9
183 Views
Last Modified: 2010-04-21
Hello,
The SQL database I'm working with is behaving strangely. There are a couple of issues, but the main one I want to ask about is what appears to be duplicate entries. I have two tables, order_items and riders. The primary key for the order_items is porder_item, and it is meant to be unique. When I query the database for results from order_items for a particular porder_item, I get one result. When I query for results from order_items inner joined with riders, I get two results, with the same porder_item. The new result row appears to be taken in part from a row that has a different porder_item value when querying the order_items table alone. I've checked my code, and porder_item is never explicitly set, just auto incremented. My first question is: Is this more likely to mean that the data is being inserted incorrectly somehow, or that it's OK but being retrieved incorrectly? My second question is: is this a symptom of a larger problem with the database, or is it more likely to be a code error somewhere?
0
Comment
Question by:bucky42
  • 5
  • 2
  • 2
9 Comments
 
LVL 11

Expert Comment

by:CMYScott
ID: 21842344
the results of the joined query are normal IF you have two rows in riders that have the same porder_item value (or are linked to the same row that contains that porder_item value)

Given that the results are correct for the database (that does not mean you are getting what you WANT - but that you are getting what you are asking SQL for) - I don't believe its an indication of anything wrong with your database.
0
 
LVL 5

Expert Comment

by:fesnyng
ID: 21842368
>>My first question is: Is this more likely to mean that the data is being inserted incorrectly somehow, or that it's OK but being retrieved incorrectly?<<

Most likely your JOIN is causing the dup.

The following should confirm if you have duplicate Primary Keys on porder_item.  If you see anything other than 1 for xcnt, then you have duplicate porder_item keys.

SELECT porder_item., count(*) as xcnt from dbo.d
GROUP BY porder_item.
ORDER BY xcnt DESC
0
 

Author Comment

by:bucky42
ID: 21842546
The query above returns 1, so it appears that there isn't a dup in the database. But how can an inner join produce a dup result?

If this is my query

SELECT * FROM Rider r INNER JOIN Order_Item o ON r.fk_category=o.fk_category WHERE o.porder_item=XXXX;

where does that extra result come from?

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Accepted Solution

by:
CMYScott earned 500 total points
ID: 21842625
your join is going to bring back all combinations of rows where Rider.fk_category and Order_Item.fk_category have the same value.

so if you have one row in Order where fk_category has a value of 1000
but you have 2 rows in Rider where where fk_category has a value of 1000

the inner join is going to return
-- the combination of the single-row result in Order with the FIRST row-result in Rider
AND
 -- the combination of the single-row result in Order with the SECOND row-result in Rider


0
 
LVL 5

Expert Comment

by:fesnyng
ID: 21842697
CMYScoot is on it.

You can determine which rows in by changing the above query a little.

BTW, if you have not guessed, I use the query below exactly for the purpose of figuring out why I have dup rows in a join when there are not "supposed" to be any dups. (And thanks for catching my copy & paster error in the earlier version).

SELECT fk_category., count(*) as xcnt from Rider
GROUP BY fk_categorym.
ORDER BY xcnt DESC
0
 

Author Comment

by:bucky42
ID: 21842752
Aaah, so I need to join on a column which is unique for both tables to get a unique result. That makes sense.
0
 

Author Comment

by:bucky42
ID: 21842827
or where the WHERE clause eliminates any unwanted rows; so there must be a WHERE clause acting on any column of a table that has multiple values for something being joined on.
0
 

Author Comment

by:bucky42
ID: 21842840
No, scratch that last part. The WHERE clause has to be on something OTHER than the column with multiple rows in the join, since obviously all those rows would satisfy the WHERE clause if it were on them.
0
 

Author Closing Comment

by:bucky42
ID: 31469588
Thanks! This revealed a major flaw in my understanding of inner joins.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Select * from 6 41
insert query with value having 's 2 51
SQL 2005 - Memory Table Column Names 11 72
SQL Restore Script - Syntax Error 8 96
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question