Re: Aggregate

David,

> The problem I have is with "gp.value < 4". You see, permission value 4
> is DENY, and anything below that is CREATE (3), EDIT (2), or READ (1).
> But this query could return *many* rows with many different permissions
> associated with one site. The issue is that if *any* permission value
> for a particular Site ID is 4 (DENY), then I need it to return *no*
> rows for that site.
<snip>
> The question is, how can I alter the first query above to get that same
> behavior -- preferably while avoiding the performance overhead of a NOT
> IN statement?

Try this:

SELECT DISTINCT a.id
FROM site a, member m, site_member c, member m3, member m4,
site_member c3, user_member um,
grp_priv__grp_member gm
WHERE a.id = c.object_id
AND c.member__id = m.id
AND m.active = 1
AND a.id = c3.object_id
AND c3.member__id = m3.id
AND m3.active = 1
AND m3.grp__id = gm.grp__id
AND gm.grp_priv__id = gp.id
AND gp.value < 4
AND gp.grp__id = m4.grp__id
AND m4.id = um.member__id
AND um.object_id = 1
AND NOT EXISTS ( select id from grp_priv gp4
WHERE gm.grp_priv__id = gp4.id
AND gp4.value = 4)
ORDER BY a.id

... though I'm a little baffled by your join structure, so I'm not sure the
crieteria in the EXISTS clause are right.

You should also make sure that your GEQO threshold on your server is set
higher than the default. I usually set mine to 24.

--
-Josh Berkus
Aglio Database Solutions
San Francisco