Search results from table and relational table using multiple search parameters

[dotty]

Hi,

I have a products table with product name, supplier, size, price etc. and a category table with assigned products.

Products can belong to multiple categories so the relationship is one to many. I want to search the products by category, supplier, size and minimum & maximum price. My current sql looks like this:

SELECT cats.*, products.*, 
FROM cats, products 
WHERE cats.catid LIKE '$_GET[catid]' 
AND products.price BETWEEN '$_GET[minprice]' AND  '$_GET[maxprice]' 
AND products.supplier LIKE '$_GET[supplier]' 
AND products.size LIKE '$_GET[size]' 

this returns the correct results from the main products table search parameters but doesn't return the correct category.

I saw some postings about UNION but I don't understand it and am not even sure if it's what I need

I'd appreciate any help :o

[dotty]

anybody??

[bastien]

Are you looking for cats that match all the criteria or any of the criteria...if the answer is any, then use OR instead of AND

[dotty]

Got it! Needed to use INNER JOIN

here 'tis - in case it helps anyone:

SELECT * FROM products  

INNER JOIN cats ON properties.propid=cats.propid
WHERE cats.catid LIKE '$_GET[catid]'  

AND products.price BETWEEN '$_GET[minprice]' AND  '$_GET[maxprice]'  

AND products.supplier LIKE '$_GET[supplier]'  

AND products.size LIKE '$_GET[size]'