homepage Welcome to WebmasterWorld Guest from 54.204.94.228
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Can I do this in just 1 query?
FiRe




msg:4041250
 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




msg:4041539
 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


Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved