Re: Aggregate

On Mon, Mar 03, 2003 at 12:45:00PM -0800, David Wheeler wrote:
> 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).

How 'bout "NOT EXISTS"?

BTW, I'm not quite clear on why you have the biggie self-join thing
w/member 3 times & site_member twice. What does it do?

HTH :)

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 cell: +1 415 235 3778