X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id CF31DDA179
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 21 Nov 2005 16:19:37 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 32342-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 21 Nov 2005 20:19:38 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 3C22BDA1EC
	for <pgsql-sql@postgresql.org>; Mon, 21 Nov 2005 16:19:34 -0400 (AST)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id E67423105A; Mon, 21 Nov 2005 21:19:34 +0100 (MET)
From: stig erikson <stigerikson_nospam_@yahoo.se>
X-Newsgroups: pgsql.sql
Subject: Re: A Not Join
Date: Mon, 21 Nov 2005 21:16:13 +0100
Organization: Hub.Org Networking Services
Lines: 42
Message-ID: <dlta4i$2hvu$1@news.hub.org>
References: <43677B35.6010005@lani.co.za>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: usenet@news.hub.org
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20050920
X-Accept-Language: en, en-gb, en-us, nn, nb, no, sv, pl
In-Reply-To: <43677B35.6010005@lani.co.za>
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.11 required=5 tests=[AWL=0.110]
X-Spam-Score: 0.11
X-Spam-Level: 
X-Archive-Number: 200511/254
X-Sequence-Number: 23505

L van der Walt wrote:
> 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.
> 
> Thus, a NOT the statement for the above SQL statement
> 
> Regards
> 
> Lani
> 

what you need to do is select all possible permissions and then remove the
permissions that exist. try somthing like:

SELECT permissions.name
FROM permissions.name
WHERE permission.permissionid NOT IN
	( SELECT permissions.permissionid
	  FROM users INNER JOIN linkuptable
	  ON (users.id = linkuptable.userid)
	  INNER JOIN permissions
	  ON (permissions.id = linkuptable.permissionid)
	  WHERE users.username = 'DummyUser'
	)