Clever sql statement

Results 1 to 2 of 2

Thread: Clever sql statement

  1. #1
    Join Date
    Dec 1969

    Default Clever sql statement

    I have a page that lists all sub-categories of products based on a selected manufacturer and a selected category. What I want is, in addition to the sub-category name, one randomly selected product from that sub-category to be displayed. The sql to get the first part is simple:<BR><BR>Select ProductSubCategoryDescription, ProductSubCategoryID FROM ProductSubCategories WHERE ProductSubCategoryManufacturerID = " & ft(request.querystring("mid")) & " AND ProductSubCategoryProductCategoryID = " & ft(request.querystring("pcid"))<BR><BR>The real example is this: Give me all sub categories from the manufacturer Seiko where the category is watches. An example of sub-categories are Dive Watch, Kinetic, etc... As Im looping thru each sub category, I want one random product that is of that sub category, manufacturer and category.<BR><BR>I dont want to open a seperate recordset for each random product. Any ideas on how I can get this with one pull?

  2. #2
    Join Date
    Dec 1969

    Default Which DBMS?

    An sql server solution<BR><BR>Select ProductSubCategoryDescription, <BR> ProductSubCategoryID <BR>(select top 1 * from products <BR> where manufactureID = 4711<BR> and subCategoryID = 32<BR> order ny newid)<BR>FROM ProductSubCategories <BR>WHERE ProductSubCategoryManufacturerID = 4711<BR> AND ProductSubCategoryProductCategoryID = 32<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts