SQL: SELECT statement

[redeyes]

I am (attempting) to create a php/mysql/flash shopping cart.. and having a small prob with a select statement. The problem is (I think), I am joining 9 tables.. but not every product has information stored in every table..

for example:

Product A has a color, a range, and a category... but it doesnt have a size.. whereas other products might have a size.. all products have a range and category.. but not all have a color and a size..

With that said.. my select statement only produces a result if the product has a color and a size.. if it doesnt.. then i get an empty set.

This is the statement I am using:

SELECT * FROM products
INNER JOIN prod_cat ON prod_cat.product_id = products.product_id
INNER JOIN category ON category.cat_id = prod_cat.cat_id
INNER JOIN prod_color ON prod_color.product_id = products.product_id
INNER JOIN color ON color.color_id = prod_color.color_id
INNER JOIN prod_range ON prod_range.product_id = products.product_id
INNER JOIN range ON range.range_id = prod_range.range_id
INNER JOIN prod_size ON prod_size.product_id = products.product_id
INNER JOIN size ON size.size_id = prod_size.size_id
WHERE products.product_id = '60'

I guess my question is.. is there a way to modify this to get what I need.. or do I have to add 'N/A' where needed (for products that dont have a size) in order to get this statement to work?

Any ideas?

[jofa]

Use LEFT JOIN