Select Query producing duplicate results of the sa

问题:

I had a select query, which I designed in the SQL view of the query design tool. With some of my results I found duplicates of the same records. As in there were not multiples in the table, only the query (Same Primary Key). Here is the original query.

SELECT t1.* 
FROM Inventory AS t1 INNER JOIN Inventory AS t2 ON
t1.Part_ID = t2.Part_ID WHERE (t1.Inventory_ID<>t2.Inventory_ID);

I aimed to query Inventory for records with the same Part_ID (FK) but different Inventory_ID(PK). There is a composite key between part_ID (FK) and location_ID (FK), if that makes any difference.

I have since changed this query to:

SELECT DISTINCT t1.*
FROM Inventory AS t1 INNER JOIN Inventory AS t2 ON t1.Part_ID = t2.Part_ID
WHERE (t1.Inventory_ID<>t2.Inventory_ID);

This removes the duplicate records, however, I don't believe that my original query should produce replicate data results. I am worried that this suggests that there is something wrong with my tables?

My table looks like the following:

Thanks

回答1:

The thing is that you might have multiple occurences of part_ID on the INNER JOIN side of your select. So if a part with the same part_ID and a different inventory_ID exists in 2 other locations, you will get duplicates.

To check that, you could do a test on a few duplicates, or rewrite your original query with a GROUP BY instruction on the INNER JOIN side of the query.