search in 3 tables

[zeberrun]

• i have table a, b, c
• in table c only for category numbers
• in table c, i have two lines, products_id and category_id.

$test = db_query("select distinct a.products_id, a.products_image, b.products_name from " . TABLE_A . " a, " . TABLE_B . " b, " . TABLE_C . " c where a.products_status = '1' and a.products_id = b.products_id order by b.products_viewed desc);

• be able to see products of the category i am in (i am in computer category to see other computer pheriphals).
• i want to do this; look for products_id in table c, find its category_id and list the products same with category_id.

rgds

[bpat1434]

So really... you want to query table C for all products that have the same category_id as some other product_id

SELECT category_id 
FROM TABLE_C 
WHERE product_id = {$some_product_id} 
LIMIT 0,1

Now, list the product information with other products with the same category_id

SELECT a.products_id, a.products_image, b.products_name
FROM TABLE_A AS a
LEFT JOIN TABLE_B AS b
    USING (products_id)
LEFT JOIN TABLE_C AS c
    ON a.products_id = c.products_id
WHERE c.category_id = (
    SELECT category_id
    FROM TABLE_C AS c
    WHERE products_id = {$some_product_id}
    LIMIT 0,1
)

The sub-query of the main query will be executed first. Then the main query will be executed and you should get a bunch of rows back with the data you wanted where the category_id will be the same as that of the current product you're viewing.

Hope that helps.

[zeberrun]

thank you very much