X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 753D0DB698; Tue, 1 Nov 2005 11:15:02 -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 81780-09; Tue, 1 Nov 2005 15:14:58 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [67.100.216.10]) by svr1.postgresql.org (Postfix) with ESMTP id 71A7CDB690; Tue, 1 Nov 2005 11:15:00 -0400 (AST) Received: by flake.decibel.org (Postfix, from userid 1001) id 0D67D15232; Tue, 1 Nov 2005 09:15:04 -0600 (CST) Date: Tue, 1 Nov 2005 09:15:04 -0600 From: "Jim C. Nasby" To: L van der Walt Cc: pgsql-sql@postgresql.org, pgsql-general@postgresql.org Subject: Re: A Not Join Message-ID: <20051101151504.GL20349@pervasive.com> References: <43677B35.6010005@lani.co.za> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <43677B35.6010005@lani.co.za> X-Operating-System: FreeBSD 4.11-RELEASE-p10 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.9i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.008 required=5 tests=[AWL=0.008] X-Spam-Score: 0.008 X-Spam-Level: X-Archive-Number: 200511/28 X-Sequence-Number: 86172 On Tue, Nov 01, 2005 at 04:27:01PM +0200, 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 LEFT JOIN permissions ON (...) WHERE permissions.id IS NULL You might have to do the NULL check in a HAVING clause instead... try it. BTW, this is probably better asked on pgsql-sql. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461