Michael Glaesemann wrote: > > On Sep 25, 2007, at 16:59 , Madison Kelly wrote: > >> SELECT >> d.dom_id, >> d.dom_name, >> (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) >> AS >> usr_count >> FROM >> domains d >> WHERE >> (SELECT COUNT(*) FROM users u WHERE u.usr_dom_id=d.dom_id) > 0 >> ORDER BY d.dom_name ASC; > > Why not just use a join? Something like this would work, I should think: > > select dom_id, > dom_name, > usr_count > from domains > natural join (select usr_dom_id as dom_id, > count(usr_dom_id) as usr_count > from users) u > where usr_count > 0 > order by dom_name;
Maybe the usr_count should be tested in a HAVING clause instead of WHERE? And put the count(*) in the result list instead of a subselect. That feels more natural to me anyway. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
