bucky42
asked on
SQL entries with duplicate primary keys
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?
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?
>>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
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
ASKER
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_categor
where does that extra result come from?
If this is my query
SELECT * FROM Rider r INNER JOIN Order_Item o ON r.fk_category=o.fk_categor
where does that extra result come from?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Aaah, so I need to join on a column which is unique for both tables to get a unique result. That makes sense.
ASKER
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.
ASKER
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.
ASKER
Thanks! This revealed a major flaw in my understanding of inner joins.
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.