help with ORDER BY

[Pedro Mpa]

Hi all!

I need some help with ORDER BY in the following example. I want to order by
selected category, then by subcategories of the selected category, then by
categories with the same parent_id of the selected category, then by random
if possible, or random within the categories if possible, but the first
order by part is not working because is not returning products of the
selected category first, instead returns products from a top category
(parent_id = 0).

table categories
id | parent_id | category
- where parent_id is 0 for top categories.

table products
id | id_category | product

SELECT 
        products.id, 
        (SELECT 
                CASE WHEN CHAR_LENGTH(products.product) > 40 THEN 
                        CONCAT(SUBSTRING(products.product,1,37),'...') 
                ELSE products.product END) AS product, 
        (SELECT 
                CASE WHEN CHAR_LENGTH(products.description) > 70 THEN 
                        CONCAT(SUBSTRING(products.description,1,67),'...') 
                ELSE products.description END) AS description 
FROM 
        products 
WHERE 
        products.id_stock = 1 
ORDER BY 
        products.id_category IN (".$id_selected_category." 
        , (SELECT categories.id 
        FROM categories 
        WHERE categories.parent_id = ".$id_selected_category.") 
        , (SELECT categories.id 
        FROM categories 
        WHERE categories.parent_id = ".$parent_id.")) 
        , RAND() LIMIT 11 

(the php vars have correct values)

Please apologise my bad English.
Thanks in advance.
Pedro.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]