Welcome to WebmasterWorld Guest from 54.242.94.72

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)

5+ Year Member



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)

10+ Year Member




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

 

Featured Threads

Hot Threads This Week

Hot Threads This Month