Can I do this in just 1 query?

Forum Moderators: open

Message Too Old, No Replies

Can I do this in just 1 query?

FiRe

9:54 am on Dec 11, 2009 (gmt 0)

SELECT COUNT(links.id) AS total_links, categories.id, categories.title FROM categories LEFT JOIN links ON categories.id = links.categoryid GROUP BY categories.id

This works fine, but I only want the total_links to count the links that are set to active.

SELECT COUNT(links.id) AS total_links, categories.id, categories.title FROM categories LEFT JOIN links ON categories.id = links.categoryid WHERE links.active = 1 GROUP BY categories.id

The problem is now that it won't show any categories that have no links to them. Is there a way to do this still in one query or would I need to run a seperate query to calculate the total_links ?

syber

6:32 pm on Dec 11, 2009 (gmt 0)


this should do it: 
  
SELECT categories.id, categories.title,  
  (SELECT COUNT(*)  
   FROM links  
   WHERE categoryid = categories.id AND active =1) AS total_links  
 FROM categories