Combine SQL queries for simplicity and performance gains

Question

I am trying to combine a few SQL queries to reduce the number of queries performed and hopefully increase performance at the same time.

I have lots of products, split into categories. Some of the products have articles written for them (most don't though). At the moment I fetch all the products for a category a bit like this...

// * used here to keep things simple
SELECT * FROM products WHERE iCategory=23;

Then, for each product in the resulting list I am doing an additional query like this...

SELECT COUNT(*) FROM articlelinks WHERE iProduct=xxx;

If there are 1 or more articles for a product, I can put a little icon next to it.

Can I combine these into a single query that would give me a count for each product, including the products where the count would be zero. All my attempts result in a list with that only includes the products with an article.

I am using MySql and PHP.

Answer 1

You can try something like this

SELECT iProduct, COUNT(articlelinks.iArticle)
FROM products LEFT JOIN
  articlelinks ON products.iProduct = articlelinks.iProduct
WHERE iCategory=23
GROUP BY iProduct

Answer 2

You can use a (scalar) subquery to get the count for each "group".

SELECT
  *,
  (
    SELECT
      COUNT(*)
    FROM
      articlelinks
    WHERE
      articlelinks.iProduct=products.iProduct
  ) as c
FROM
  products
WHERE
  iCategory=23

Answer 3

You could use a select statement like this :

Select iProduct,  
[other cols],    
(select count(*) from articlelinks where iProduct = products.iProduct) as articles  
from products   
WHERE iCategory=23;

This should always give you an article count, even if it is zero