Hi
I am having trouble creating an SQL statement to get what I want. I have three tables in a database: products, options and productoptions.
products holds product details
options hold data on options which can come with products
productoptions links options to products and only has two columns apart from the key: productId (key from products) and optionId (key from options)
The SQL statement I am trying to write should show all products plus the number of associated options (if any) for a given category.
What I have so far is:
SELECT products.productId,
products.image_thumb,
products.name,
products.ordercode,
products.short_desc,
products.price,
COUNT(productoption.optionId) as optionCount
FROM products, productoption
WHERE products.category = ' . $this->categoryId . '
AND products.productId = productoption.productId
GROUP BY products.productId ';
This gives me the data I want, but only returns products which have at least one option (ie. it's productId appears at least once in the productoptions table).
How can I change the SQL statement so that all products are shown regardless of whether that prodcut's productId appears in productoptions? I just need optionCount to be zero if it doesn't appear.
fergus
