SQL RAND Query selecting all records and not using joined table

Question

Please can someone talk me through a query that I have inherited through a website I am developing.

The query is returning a random group of 5 products based on the category number 56. There is an issue with the query because it is not restricting the selection based on the product on web and product archive conditions.

AND p.product_OnWeb = 1
AND p.product_Archive = 0

The above lines in the query aren't being adhered to. Instead the query is including all products even when they are marked as p.product_Archive=1 (Archived) and p.product_OnWeb = 0 (Not Online)

If someone could point out where I need to make a change I'd be grateful.

The query in full is:-

SELECT c.prdt_cat_rel_Product_ID,
ROUND(RAND() * x.m_id) 'rand_ind'
FROM tbl_prdtcat_rel c,
tbl_products p,
(SELECT MAX(t.prdt_cat_rel_Cat_ID) 'm_id'
FROM tbl_prdtcat_rel t) x
WHERE c.prdt_cat_rel_Cat_ID = 56
AND p.product_OnWeb = 1
AND p.product_Archive = 0
ORDER BY rand_ind
LIMIT 3

Thankyou

What do you mean by *not obeying the product on web and product archive conditions*? What is your expected result ? Please show table schema as well as sample data please. — Raptor, Apr 28 '14 at 07:35
show the result you are getting, and the result you are expecting. otherwise we cannot understand what "obey" means; — Sharky, Apr 28 '14 at 07:43
still, if we do not know the meaning of these 2 fields (`product_OnWeb` & `product_Archive`), we don't know how to help. — Raptor, Apr 28 '14 at 07:44
@matthew Read about joins. You have a cross join there. Probably not what you want... — fancyPants, Apr 28 '14 at 07:44
By doing `ROUND(RAND() * x.m_id)` you're not really helping yourself. First, rounding the random number will increase your chances of duplicates. Second, you shouldn't need to multiply (by anything, really), as for sufficiently large values you can _also_ get duplicates (floating-point has some non-intuitive behavior). The regular `[0, 1)` range would be sufficient. In However, it's [fastest to get random rows one-at-a-time](http://stackoverflow.com/questions/4329396/mysql-select-10-random-rows-from-600k-rows-fast/4329447#4329447), as `ORDER BY RAND()` will require sorting the entire table — Clockwork-Muse, Apr 28 '14 at 11:35

score 0 · Answer 1 · answered Apr 28 '14 at 11:01

First, to make it easier, convert the query to the newer join syntax

 SELECT c.prdt_cat_rel_Product_ID,    ROUND(RAND() * x.m_id) 'rand_ind'
  FROM tbl_prdtcat_rel c,
  JOIN tbl_products p ON p.??? = c.???
  JOIN (
     SELECT MAX(t.prdt_cat_rel_Cat_ID) 'm_id' FROM tbl_prdtcat_rel t
       ) x  ON 1=1
  WHERE c.prdt_cat_rel_Cat_ID = 56
    AND p.product_OnWeb = 1
    AND p.product_Archive = 0
    ORDER BY rand_ind
    LIMIT 3

You can see that the query doesn't know how to select match records from P based on C, so it grabs them all.. You need to specify how to match tbl_prdtcat records with tbl_product records (replace the ??? in the above with the appropriate fields)

I am guessing each product in p has some sort of field indicating which category it belong to, use this field to match and your query should work...

SQL RAND Query selecting all records and not using joined table

1 Answers1