"Madison Kelly" <[EMAIL PROTECTED]> writes: > SELECT d.dom_id, d.dom_name FROM domains d WHERE (SELECT COUNT(*) FROM users u > WHERE u.usr_dom_id=d.dom_id) > 0 ORDER BY d.dom_name ASC; > > Which gives me just the domains with at least one user under them, but not > the count. This is not ideal, and I will have to come back to it next week. In > the meantime, any idea what the GROUP BY error is? If not, I'll read through > the docs on 'GROUP'ing once I get this deadline out of the way.
I think you just want simply: SELECT dom_id, dom_name, count(*) FROM users JOIN domains ON (usr_dom_id=dom_id) GROUP BY dom_id, dom_nmae ORDER BY dom_name You don't actually need the HAVING (though it wouldn't do any harm either) since only domains which match a user will come out of the join anyways. You can also write it using a subquery instead of a join SELECT * FROM ( SELECT dom_id, dom_name, (SELECT count(*) FROM users WHERE user_dom_id = dom_id) as nusers FROM domains ) as subq WHERE nusers > 0 ORDER BY dom_name But that will perform worse in many cases. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(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