Message-ID: <44CDB1EF.6000508@debraneys.com>
Date: Mon, 31 Jul 2006 15:31:59 +0800
From: John Tregea <john@debraneys.com>
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Joining a result set from four (4) tables
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

Can anyone help me with the following?

I am setting up a series of permissions of my own making in pgSQL 8.1.4. 
I have the following tables;

resource -- a list of available resources
actions -- the actions available to the user
policies -- the actions that are allowed to be performed on individual 
resources
permissions -- matches users  with granted actions on any resource
users --  no surprises here


I have read the docs about joins but cannot get my head around the 
correct syntax. The following SQL returns all actions for every resource

SELECT
   permission.serial_id,
   resource.name,
   actions.name,
   actions.classification,
   actions.display_group,

FROM
   permission, policies, resource, actions

WHERE
   permission.user_id = '11' AND
   permission.related_id = policies.serial_id AND
   policies.status = 'Active' AND
   permission.status = 'Active'AND
   actions.status = 'Active'AND
   resource.status = 'Active'

I need a list of permissions back for each resource that a user is 
authorised to access (when they login to their GUI).

I also need to check (at user login) if every record in the chain (e.g. 
resource, action, policy and permission) is "Active" before the 
permission record is considered valid.

The list for a resource called 'Scenarios' would look something like:

11900;"Scenarios";"Publish";"Action";"B"
11900;"Scenarios";"Authorise";"Action";"B"
11900;"Scenarios";"Create";"Action";"C"
11900;"Scenarios";"Update";"Action";"C"

I am guessing it should be an inner join? but by reference book does not 
show joins on this many tables.

Thanks in advance for any help.

Regards

John T