Re: how to show 6 randomly selected featured products?

Hi, Joins are fine to use, the problem here is you have them in the wrong order. SELECT p.prod_id, p.prod_name, p.prod_no, chp.cat_id, c.cat_name, c.cat_parent, pr.price FROM products as p LEFT JOIN categories_has_products as chp ON chp.prod_id = p.prod_id LEFT JOIN categories as c ON chp.cat_id = c.cat_id LEFT JOIN prices as pr ON pr.prod_id = p.prod_id WHERE prod_featured = '1' ORDER BY RAND(), pr.price ASC LIMIT 6 This is doing: join products to categories_has_products (using prod_id) -- fine then join categories_has_products to categories (using cat_id) -- fine then join categories to prices (using prod_id) --- not fine You could try SELECT p.prod_id, p.prod_name, p.prod_no, chp.cat_id, c.cat_name, c.cat_parent, pr.price FROM products as p LEFT JOIN prices as pr ON pr.prod_id = p.prod_id LEFT JOIN categories_has_products as chp ON chp.prod_id = p.prod_id LEFT JOIN categories as c ON chp.cat_id = c.cat_id and it should work quickly because it does: join products to prices (using prod_id) then join prices to categories_has_products (using prod_id) then join categories_has_products to categories (using cat_id) Afan Pasalic wrote:

?!?!? Yup. It works! YEAH!!! :) :) :) And pretty fast too! Ok. I was suggested to use JOIN. But you DON'T use it? -afan On Thu, 2006-02-09 at 13:09 +1100, Chris wrote:

Oops, missed this one before I sent that other email. I think it's the order of your joins playing up. LEFT JOIN categories as c ON chp.cat_id = c.cat_id LEFT JOIN prices as pr ON pr.prod_id = p.prod_id That means it will join categories -> prices - not what you want. Try this query: SELECT p.prod_id, p.prod_name, p.prod_no, chp.cat_id, c.cat_name, c.cat_parent, pr.price FROM products p, categories_has_products chp, categories c, prices pr WHERE chp.prod_id = p.prod_id AND chp.cat_id = c.cat_id AND pr.prod_id = p.prod_id AND prod_featured = '1' ORDER BY RAND(), pr.price ASC LIMIT 6 Afan Pasalic wrote:

The result display is bad (at least on at email). Here is a snapshot: http://afan.net/bhw/snapshot1.png -afan On Wed, 2006-02-08 at 19:39 -0500, Afan Pasalic wrote:

Hm. Have no idea what does it means: table type possible_keys key key_len ref rows Extra p ALL NULL NULL NULL NULL 1268 Using where; Using temporary; Using filesort chp index NULL PRIMARY

      8

NULL 1741 Using index c eq_ref PRIMARY PRIMARY

      4

chp.cat_id

      1

pr ALL NULL NULL NULL NULL 10231 -afan On Thu, 2006-02-09 at 09:53 +1100, Chris wrote:

Hi, Grab that query and run it through mysql with 'explain', so: explain SELECT p.prod_id, p.prod_name, p.prod_no, chp.cat_id, c.cat_name, c.cat_parent, pr.price

FROM products as p

LEFT JOIN categories_has_products as chp ON chp.prod_id = p.prod_id LEFT JOIN categories as c ON chp.cat_id = c.cat_id LEFT JOIN prices as pr ON pr.prod_id = p.prod_id WHERE prod_featured = '1' ORDER BY RAND(), pr.price ASC LIMIT 6 That will show you whether you are using the database indexes or not. If you can't work it out then post the explain output.