mysql conditional/subquery for 4.0?

[viveleroi0]

I have a MySQL 4.0 table that lists items for sale. These items can be linked to up to three categories.

The product table has three columns like 'prod_category_1', 'prod_category_2', 'prod_category_3' which can hold an ID linked to the category table.

Each category can be active or inactive, which is just a field with either a 1 or 0. My problem is that I need to display products who have at least one active category.

I am required to use MySQL 4.0, so subqueries and conditional CASE statements are not available. I tried using:

SELECT * FROM products, categories
WHERE categories.is_active = 1
AND (categories.cat_name =products.prod_category_1
    OR categories.cat_name = products.prod_category_2
    OR categories.cat_name = products.prod_category_3);

But the query returns multiple copies of the same product for each category it's in. I only want to check if at least one of the categories it's linked to is set to active. If it only has one category and that category is inactive, no results should be returned.

I can solve this by using features present in MySQL 4.1+, but how do I do so in 4.0?

[devinemke]

rather than using 3 separate fields you could use a SET field that defaults to NULL? then you query would simply be:

SELECT * FROM products WHERE category IS NOT NULL

[viveleroi0]

The three fields link the product to up to three different categories from another table. I need to check if at least one of those connected categories is set to ACTIVE.

[MarkR]

You can do this with no subqueries - simply use outer joins to join three copies of the category table, put in the appropriate WHERE clause, then use

WHERE c1.active = 1 OR c2.active = 1 OR c3.active = 1

(c1,c2 and c3 are all different aliases of the category table you've outer joined).

Mark