Aggregate

Hi All,

I need a little advice from the SQL pros on the list. I have this query
in Bricolage:

SELECT DISTINCT a.id
FROM site a, member m, site_member c, member m3, member m4,
site_member c3, user_member um,grp_priv gp,
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
ORDER BY a.id

It is designed to get a list of site IDs for a given user ID. It does
this by going through Bricolage's group-based permissions system. The
complexity of the join isn't what I'm worried about (I think that it's
relatively compact).

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.

So, to simplify, a site can have many permissions. If I just had one
table matching site IDs to permission values, the query that would do
what I want might look like this:

SELECT DISTINCT site__id
FROM site__permission
WHERE value < 4
AND site__id NOT IN (
SELECT site__id
FROM site__permission
WHERE value = 4
)

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?

TIA for the help!

David

--
David Wheeler AIM: dwTheory
david(at)kineticode(dot)com ICQ: 15726394
Yahoo!: dew7e
Jabber: Theory(at)jabber(dot)org
Kineticode. Setting knowledge in motion.[sm]