Re: Joining a result set from four (4) tables - Mailing list pgsql-sql
| From | Aaron Bono |
|---|---|
| Subject | Re: Joining a result set from four (4) tables |
| Date | |
| Msg-id | bf05e51c0608011232jc6cae94wa82f3e6148766484@mail.gmail.com Whole thread Raw |
| In response to | Re: Joining a result set from four (4) tables (John Tregea <john@debraneys.com>) |
| Responses |
Re: Joining a result set from four (4) tables
|
| List | pgsql-sql |
On 7/31/06, John Tregea <john@debraneys.com> wrote:
First of all, I must say it is very unfortunate that all the tables have the same name for their primary key column. It made things confusing at first when reading through the tables (the foreign key names don't match the primary keys they map to). It also makes it more likely you will do a join improperly.
Worse, your foriegn key names are very ambiguous. The name related_id says nothing about what table it maps to. That means you need documentation or the foreign key definitions (are you using foreign key constraints?) to tell what is going on.
Anyway... on to solving your problem.
SELECT
permission.serial_id,
resource.name,
actions.name,
actions.classification,
actions.display_group
FROM permission
INNER JOIN policies ON (
policies.serial_id = permission.related_id
)
INNER JOIN actions ON (
actions.serial_id = policies.related_id
)
INNER JOIN resource ON (
-- This is tricky as policies maps to resources AND actions maps to resources
-- so the real question is which one do you do? I did both.
policies.resource_id = resources.serial_id
AND
actions.related_id = resources.serial_id
)
WHERE
permission.user_id = '11' AND
policies.status = 'Active' AND
permission.status = 'Active'AND
actions.status = 'Active'AND
resource.status = 'Active'
;
I always discourage listing more than one table in the FROM clause. Use INNER and OUTER JOINs - it is much easier to debug and it is somewhat self documenting. That way, when you or another developer look at this in the future, you understand right away how the tables are being put together.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================
Hi aaron,
Here are the 'create table' statements. I have indicated what are the
primary and foreign keys with trailing comments.
Thanks
John
Aaron Bono wrote:
> Can you include the table create statements with primary and foreign
> keys? That would help a lot.
CREATE TABLE resources
(
serial_id numeric NOT NULL, -- << Primary Key
related_id numeric, -- << Foreign Key
host_id int4,
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
schema_name varchar(32),
grid_loc varchar(32),
name varchar(32),
status varchar(16),
description text,
comments text,
sort_order int2,
user_id int4 DEFAULT 0,
located text,
classification varchar(32),
sequence_id int4,
)
CREATE TABLE actions
(
serial_id numeric NOT NULL, -- primary key
related_id numeric, -- foreign key on resources.serial_id
host_id int4,
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
name varchar(32),
status varchar(16) DEFAULT 'Active'::character varying,
description text,
comments text,
sort_order int2 DEFAULT 0,
user_id int4 DEFAULT 0, -- User_ID of the creator
located text,
classification varchar(32),
sequence_id int4,
in_box varchar(32),
display_group varchar(2),
)
CREATE TABLE policies
(
serial_id numeric NOT NULL, -- primary key
related_id numeric, -- foreign key on actions.serial_id
resource_id numeric, -- foreign key on resources.serial_id
owner_id numeric,
authority_id int4,
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
status varchar(16) DEFAULT 'Active'::character varying,
description text,
comments text,
classification varchar(32),
user_id int4,
sequence_id int4,
inheritance text,
)
CREATE TABLE permissions
(
serial_id numeric NOT NULL, -- primary key
related_id numeric, -- foreign key on policies.serial_id
user_id int4, -- foreign key on users.serial_id
owner_id int4,
authority_id int4,
resource_id int4,
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
name varchar(32),
acronym varchar(6),
status varchar(16) DEFAULT 'Active'::character varying,
inheritance text,
description text,
comments text,
sort_order int2,
user_id int4 DEFAULT 0,
located text,
classification varchar(32),
sequence_id int4,
)
CREATE TABLE users
(
serial_id numeric NOT NULL, -- primary key
created timestamptz DEFAULT now(),
modified timestamptz,
valid_from timestamp,
valid_to timestamp,
name varchar(64) NOT NULL,
acronym varchar(6),
status varchar(16),
inheritance text,
description text NOT NULL,
comments text NOT NULL,
sort_order int2 NOT NULL,
clearance varchar(32) NOT NULL,
administrator bool DEFAULT false,
user_id int4 DEFAULT 0,
next_serial_id int4 DEFAULT 1,
classification varchar(32),
)
First of all, I must say it is very unfortunate that all the tables have the same name for their primary key column. It made things confusing at first when reading through the tables (the foreign key names don't match the primary keys they map to). It also makes it more likely you will do a join improperly.
Worse, your foriegn key names are very ambiguous. The name related_id says nothing about what table it maps to. That means you need documentation or the foreign key definitions (are you using foreign key constraints?) to tell what is going on.
Anyway... on to solving your problem.
SELECT
permission.serial_id,
resource.name,
actions.name,
actions.classification,
actions.display_group
FROM permission
INNER JOIN policies ON (
policies.serial_id = permission.related_id
)
INNER JOIN actions ON (
actions.serial_id = policies.related_id
)
INNER JOIN resource ON (
-- This is tricky as policies maps to resources AND actions maps to resources
-- so the real question is which one do you do? I did both.
policies.resource_id = resources.serial_id
AND
actions.related_id = resources.serial_id
)
WHERE
permission.user_id = '11' AND
policies.status = 'Active' AND
permission.status = 'Active'AND
actions.status = 'Active'AND
resource.status = 'Active'
;
I always discourage listing more than one table in the FROM clause. Use INNER and OUTER JOINs - it is much easier to debug and it is somewhat self documenting. That way, when you or another developer look at this in the future, you understand right away how the tables are being put together.
==================================================================
Aaron Bono
Aranya Software Technologies, Inc.
http://www.aranya.com
==================================================================