From: John Tregea <john@debraneys.com>
To: pgsql-sql@postgresql.org
Subject: Joining a result set from four (4) tables
Date: Mon, 31 Jul 2006 15:31:59 +0800
Message-ID: <44CDB1EF.6000508@debraneys.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: john@debraneys.com
Subject: Re: Joining a result set from four (4) tables
In-Reply-To: <44CDB1EF.6000508@debraneys.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox