CroNiX Posted February 5, 2009 Share Posted February 5, 2009 I need some help with a query that I haven't done before and have not yet succeeded. I have 2 tables. blog blog_comments ----- ------------ id id entry blog_id ... visible I am trying to: 1) select everything from blog 2) count comments left for each blog.entry 3) count comments left for each blog.entry where visible = 0 I got 1 and 2 accomplished using the following statement and need help getting #3. SELECT blog.id, blog.entry, Count(blog_comments.id) AS comment_total, FROM blog Left Join blog_comments ON blog.id = blog_comments.blog_id GROUP BY blog.id, blog.entry ORDER BY blog.`date` DESC Obviously the tables have a lot more fields, but this is the needed info to accomplish what I am after. If anybody could help I would appreciate it. Temporarily I am running 2 querries to get the data I need for #3 but would like to just have 1 statement and would like to learn this. Thanks for any insight! Quote Link to comment Share on other sites More sharing options...
rhodesa Posted February 5, 2009 Share Posted February 5, 2009 hum...is visible either 1 or 0...what about this: SELECT blog.id, blog.entry, Count(blog_comments.id) AS comment_total, COUNT(blog_comments.visible) - SUM(blog_comments.visible) AS nonvisible_total FROM blog Left Join blog_comments ON blog.id = blog_comments.blog_id GROUP BY blog.id, blog.entry ORDER BY blog.`date` DESC Quote Link to comment Share on other sites More sharing options...
CroNiX Posted February 5, 2009 Author Share Posted February 5, 2009 Well gee, you make it so easy when you type it like that Thank you once again Aaron, works great! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.