Welcome to WebmasterWorld Guest from 54.196.232.162

Forum Moderators: open

Message Too Old, No Replies

Can I do this in just 1 query?

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

Full Member

10+ Year Member

joined:Aug 9, 2005
posts:240
votes: 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 ?

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

Preferred Member

10+ Year Member

joined:Nov 12, 2004
posts: 393
votes: 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