From: | Jerry Sievers <jerry(at)jerrysievers(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: A Not Join |
Date: | 2005-11-02 02:28:22 |
Message-ID: | m34q6v7ph5.fsf@prod01.jerrysievers.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-sql |
L van der Walt <mailing(at)lani(dot)co(dot)za> writes:
> I have three table:
> Users - Contains username, ID etc...
> Permissions - A permission name and ID
> Link up table - The user.id and permission.id
>
> If a user.id and a permission.id row exists in the linkuptable the user
> have that permission granted.
>
> With the statement below I can see the permissions a user have.
>
> SELECT users.username, permissions.name
> FROM users INNER JOIN linkuptable
> ON (users.id = linkuptable.userid)
> INNER JOIN permissions
> ON (permissions.id = linkuptable.permissionid)
> WHERE users.username = 'DummyUser'
>
> How do I see the permissions that user DON'T have with a fast SQL statement.
>
Simple.
select permname
from permissions
where permid not in (
select permid
from linkage
where userid = 'dummy'
);
Or... a slick way to get the anti-permission for the whole bunch of
users is to;
cross join the userids with permids and then EXCEPT SELECT from
linkage table to filter out the active permissions.
Have fun!
--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) WWW ECommerce Consultant
305 321-1144 (mobile http://www.JerrySievers.com/
From | Date | Subject | |
---|---|---|---|
Next Message | Tony Caduto | 2005-11-02 03:34:35 | Part of original Postgresql cook book back online |
Previous Message | Douglas McNaught | 2005-11-02 02:07:05 | Re: Cannot install -- "/lib/cpp" failed sanity check |
From | Date | Subject | |
---|---|---|---|
Next Message | Mario Splivalo | 2005-11-02 09:26:27 | Re: PGSQL encryption functions |
Previous Message | Michael Fuhr | 2005-11-02 01:08:02 | Re: Index lookup on > and < criteria |