Skip navigation
Currently Being Moderated

single record per database field

Apr 30, 2012 10:51 PM

For the following query, I'd like to ensure that only one record per ManufacturerParNumber results as currently there are duplicates. How can the code be modified accordingly?

 

"SELECT OptionsHeaders.ID AS OptionsHeadersID, SortOrder, Products.ID AS ProductsID, Products_1.ID AS Products1ID, url_img, ManufacturerPartNumber, Description, ? FROM accessories WHERE Products_1.ID = ? OR ProductFamily=? ORDER BY SortOrder DESC"

 
Replies
  • Currently Being Moderated
    Apr 30, 2012 11:31 PM   in reply to aonefun

    Please explain what you mean by duplicates. If your entire select list is duplicated, then you can use the DISTINCT keyword to remove duplicates. If the other columns are different for a given ManufacturerPartNumber, then you need to decide which ManufacturerPartNumber you want to include. Otherwise it makes no logical sense to include those other columns.

     

    In other words, if you have two OptionHeaders for a given ManufacturerPartNumber, which OptionHeader do you want to display?

     
    |
    Mark as:
  • Currently Being Moderated
    May 1, 2012 7:31 AM   in reply to bregent

    I'm also very confused with your naming conventions.

     

    "OptionsHeaders.ID" appears to be a table called OptionsHeaders and a column called "ID", yet you are only selecting from a table called accessories. What's up with that?

     
    |
    Mark as:
  • Currently Being Moderated
    May 1, 2012 2:27 PM   in reply to aonefun

    >For filtering against the querystring, i would need both these

    >records to result in the recordset but for displaing on the page,

     

    Sorry, I'm not following you. What info are you passing in the querystring? And is this used in the WHERE clause of the SQL you posted?

     

    >i would only want either one of the above records (one record per ManufacturePartNumber)

    >since only the fields that are the same for both records are the ones displayed on the page.

     

    Then perhaps you need two recordsets. One for what you want to display on the page, and the other for, well, whatever it is you need it for   Some more context of what you're doing would be very helpful.

     

    This will give you one row for each MPN given the data above.  So I still need to know what you need those other columns for.

     

    "SELECT DISTINCT Products.ID AS ProductsID,  ManufacturerPartNumber FROM accessories WHERE Products_1.ID = ? OR ProductFamily=? ORDER BY SortOrder DESC"

     
    |
    Mark as:
  • Currently Being Moderated
    May 2, 2012 10:40 AM   in reply to aonefun

    >The first 3 fields are rendered on the actual webpage for the product description

     

    OK, no problem here as the MPN, image url, and descriptions are the same for each row, right?

     

     

    >and the next 2 consecutive fields are used for querystring filtering as indicated above.

     

    The querystring that I see in the results is using only ProductID. I don't see where Products1ID or ProductFamily is being used.  Can you explain the difference between ProductsID and Products1ID? 

     

    "http://www.canchair.com/productfamily_processor.asp?ProductID=1041"

     

     

    >SortOrder is used for ORDER BY and ProductsID is required for some reason otherwise no records display at all.

     

    Can you try this query:

     

    SELECT DISTINCT ManufacturerPartNumber, url_img, Description, Products.ID AS ProductsID

    FROM accessories

    WHERE Products_1.ID = Request.QueryString("ProductID")

    OR ProductFamily=Request.QueryString("ProductFamily")

    ORDER BY SortOrder DESC

     

     

    If that works however, I'd be tempted to fix this at the accessory query in Access as it should not return duplicate products, unless of course it is used in other queries where those additional columns are required.

     
    |
    Mark as:
  • Currently Being Moderated
    May 7, 2012 3:00 PM   in reply to aonefun

    OK, so I think I understand your data model a little better. Can you try this:

     

    SELECT DISTINCT ManufacturerPartNumber, url_img, Description, Products.ID AS ProductsID

    FROM accessories

    WHERE Products.ID = Request.QueryString("ProductID")

    OR ProductFamily=Request.QueryString("ProductFamily")

    ORDER BY SortOrder DESC

     
    |
    Mark as:
  • Currently Being Moderated
    May 7, 2012 9:59 PM   in reply to aonefun

    You do not need to use a GROUP BY since you are not aggregating anything. The DISTINCT keyword should eliminate the duplicate rows. If you are getting zero rows then the problem is with your WHERE clause. Unfortunately, most of the links you have provided do not seem to work for me so it's difficult to understand the workflow. Let me make sure I understand what your problem is:

     

    You have a page that returns a recordset that contains one item. The page also has another recordset that lists related items.  Or, on some pages I see various color options listed. Which is the recordset that you are having problems with? What are the related columns between the two recordsets?

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 1, 2012 9:26 AM   in reply to aonefun

    If adding the DISTINCT keyword causes 0 rows to be returned, then there's a problem somewhere else. Rather than trying to troubleshoot the recordset/webpage, lets work directly in MS Access. Can you please create the exact same query and include the filter values for ProductID and ProductFamily that you are testing. Post the resulting rows.

    Then, in the query properties toggle "Unique Values" to Yes and execute again. Post those results.

     
    |
    Mark as:
  • Currently Being Moderated
    Jun 21, 2012 3:30 PM   in reply to aonefun

    Why is your result set returning columns #1 and #4 ?

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points