Skip site navigation (1) Skip section navigation (2)

Re: Aggregate

From: David Fetter <david(at)fetter(dot)org>
To: SF Postgres <sfpug(at)postgresql(dot)org>
Subject: Re: Aggregate
Date: 2003-03-03 21:07:51
Message-ID: 20030303210751.GH21541@fetter.org (view raw or flat)
Thread:
Lists: sfpug
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

In response to

  • Aggregate at 2003-03-03 20:45:00 from David Wheeler

Responses

sfpug by date

Next:From: Stephan SzaboDate: 2003-03-03 21:08:48
Subject: Re: Aggregate
Previous:From: Stephan SzaboDate: 2003-03-03 21:06:29
Subject: Re: Aggregate

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group