SQL Selection of products in a single category

Question

So here is what I am trying to do. My boss wants to put all vehicles that we have on our homepage and randomly pull 8 of them at a time. The way our database schema is setup has the products, and categories in separate tables using a cross reference to locate the category the product falls under. The table with the categories has a parent that is a direct ID from another category. So here is the SQL that I came up with.

  SELECT      product.productID, 
              product.productSKU, 
              product.price, 
              product.name,
             product.stateInd, 
              category.parentID,
              category.categoryID,
              prod_cat.productID FROM category

LEFT JOIN prod_cat
     ON prod_cat.categoryID = category.categoryID
LEFT JOIN product
     ON product.productID = prod_cat.productID

WHERE category.parentID =  <cfqueryparam value="#catID#" cfsqltype="cf_sql_varchar" /> AND product.name <> "" AND RAND() 

LIMIT 8

I hope that all makes sense. I am just having the hardest time not only pulling 8 products but also making sure those 8 products are unique. Oh and I did try putting DISTINCT after the select but the product was still selected twice.

Thanks!

Answer 1

DISTINCT should work. If it doesn't, try to group by productId. To select random rows, order by rand() instead of the where rand() construct.

Combining the two:

WHERE  category.parentID = <cfqueryparam value="#catID#" cfsqltype="cf_sql_varchar"/>
       AND product.name <> ""
GROUP BY
       product.productID
ORDER BY
       RAND() 
LIMIT  8

Answer 2

I'd pull the 8 unique products in a sub-query, then use that in the rest of your query.

In very rough pseudo-code:

select ...
    from (select 8 distinct random product IDs from product table) p
        left join prod_cat
           on p.productID = prod_cat.productID
        left join category
           on prod_cat.categoryID = category.categoryID
    ...

Answer 3

NOTE: Joe beat me for this answer, but I was not sure how to put code in comment, so here is my answer.

I am not sure if categoryID will give single row or multiple rows for your input, if it is single row, you can very well use = instead of in

SELECT *
  FROM (SELECT * FROM product
         WHERE product.productID in (
            SELECT productID 
              FROM prod_cat
             WHERE categoryID in (  
                   SELECT categoryID 
                     FROM category 
                    WHERE parentID = 
                          <cfqueryparam 
                           value="#catID#" 
                           cfsqltype="cf_sql_varchar" /> 
                             )
               AND name <> ''
             ORDER BY rand()
             LIMIT 8
              ) p
INNER JOIN prod_cat ON p.productID=prod_cat.productID
INNER JOIN category ON prod_cat.categoryID=category.categoryID