locked
eliminate duplicated entries RRS feed

  • 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.



    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.



    Wednesday, October 16, 2013 10:06 AM