Answered by:
eliminate duplicated entries

Question
-
how can the following query be adjusted to ensure there are no two records containing the same Products.ID value.
SELECT DISTINCT SortOrder, Products_1.ID, Products.ID AS ProductsID, accessories.url_img, ManufacturerPartNumber, Description
FROM accessories
Tuesday, October 15, 2013 4:07 PM
Answers
-
Check this :
If the data which has the same Products.ID should not show, you can try the one below:
select DISTINCT SortOrder, Products_1.ID, Products.ID AS ProductsID, accessories.url_img, ManufacturerPartNumber, Description from accessories
where ProductsID not in (select Products.ID from accessories group by Products.ID having count(Products.ID ) >1)
Nice day. : )
remember make the reply as answer and vote the reply as helpful if it helps.
- Edited by Risa- Wednesday, October 16, 2013 10:40 AM
- Marked as answer by Fei XueMicrosoft employee Wednesday, October 23, 2013 11:18 AM
Wednesday, October 16, 2013 10:06 AM
All replies
-
What you are asking and what you are showing are completely different. What you are showing will only tell you what products are in the table. Just use the query wizard and build a find duplicates query using ID as the field to look for duplicates IN. Now, once you have them down to 1, put an index on the field that prohibits duplicates. Without knowing everything about your database, I can't be sure, but it does look like it should be the table's primary key.Tuesday, October 15, 2013 5:55 PM
-
aonefun,
In fields list in select you have field ID from table Products, in FROM you get data from table accessories...
This query is not complete.
Michał
Tuesday, October 15, 2013 7:03 PM -
Check this :
If the data which has the same Products.ID should not show, you can try the one below:
select DISTINCT SortOrder, Products_1.ID, Products.ID AS ProductsID, accessories.url_img, ManufacturerPartNumber, Description from accessories
where ProductsID not in (select Products.ID from accessories group by Products.ID having count(Products.ID ) >1)
Nice day. : )
remember make the reply as answer and vote the reply as helpful if it helps.
- Edited by Risa- Wednesday, October 16, 2013 10:40 AM
- Marked as answer by Fei XueMicrosoft employee Wednesday, October 23, 2013 11:18 AM
Wednesday, October 16, 2013 10:06 AM