pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFinding matching columns from two tables
3+ messages / 2 participants
[nested] [flat]
* Finding matching columns from two tables
@ 2002-02-21 20:17 Richard Emberson <emberson@phc.net>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Emberson @ 2002-02-21 20:17 UTC (permalink / raw)
To: pgsql-sql
Below can be included in a psql via the \i command.
There are basically two tables (and some others for ref-int): membership
and group_sets.
A user (user_id) is a member of one or more groups (group_id). Groups
are also organized into
group sets (group_set_id) in the group_sets table. The task is to find
the group_set_id for a set
of groups, if it exists, given a user_id, and the associated groups that
user is a member of.
The function, get_group_set_id(), at the end is my attempt. It works
but its rather convoluted and
requires 4 SELECT statements.
Any help.
Thanks.
Richard
----------------------------------------------------------------
/*
In some cases, the group_ids associated with a group_set_id are the
same as the group_ids associated with a user_id. For a given user_id
determine if there is a corresponding group_set_id.
Find the group_set_id such that for a given user_id (input parameter)
there is a one-to-one correspondence between the group_ids associated
with the user_id and the group_ids associated with the group_set_id;
for every group_id that the user_id has, the group_set_id also has it
and for every group_id that the group_set_id has, the user_id also has
it.
If there is no such group_set_id, then return null.
What query will generate the group_set_id?
The function get_group_set_id() is my attempt. Is there a better way?
The fact that it has 4 SELECT statement in it is, I expect, far from
optimum.
SELECT get_group_set_id(10); -- should be 30
SELECT get_group_set_id(11); -- should be -1
SELECT get_group_set_id(12); -- should be -1
SELECT get_group_set_id(13); -- should be 31
*/
-- all of the users
-- user_id are unique
DROP TABLE users;
CREATE TABLE users (
user_id BIGINT,
-- other columns
PRIMARY KEY (user_id)
);
COPY users FROM stdin USING DELIMITERS ':';
10
11
12
13
\.
-- all of the groups
-- user_id are unique
DROP TABLE groups;
CREATE TABLE groups (
group_id BIGINT,
-- other columns
PRIMARY KEY (group_id)
);
COPY groups FROM stdin USING DELIMITERS ':';
20
21
22
23
24
\.
-- a user can be a member of one or more groups
DROP TABLE membership;
CREATE TABLE membership (
user_id BIGINT,
group_id BIGINT,
-- other columns
FOREIGN KEY (user_id) REFERENCES users (user_id),
FOREIGN KEY (group_id) REFERENCES groups (group_id)
);
COPY membership FROM stdin USING DELIMITERS ':';
10:20
10:21
11:20
12:21
12:22
12:23
13:20
13:22
\.
-- a group set is a set of one or more groups
DROP TABLE group_sets;
CREATE TABLE group_sets (
group_set_id BIGINT,
group_id BIGINT,
-- other columns
FOREIGN KEY (group_id) REFERENCES groups (group_id)
-- FOREIGN KEY (group_set_id) REFERENCES group_sets (group_set_id)
);
COPY group_sets FROM stdin USING DELIMITERS ':';
30:20
30:21
31:20
31:22
32:20
32:21
32:22
32:24
\.CREATE OR REPLACE FUNCTION get_group_set_id (
BIGINT
)
RETURNS BIGINT AS '
DECLARE
-- parameters
user_id_p ALIAS FOR $1;
-- local variables
group_id_count_v INTEGER;
count_v INTEGER;
group_set_id_v BIGINT;
foo group_sets%ROWTYPE;
BEGIN
-- how many groups were given to this user
SELECT INTO group_id_count_v count(*) FROM membership
WHERE user_id = user_id_p;
-- select all that have groups shared by the linked user and
-- have the right number (or more)
FOR foo IN SELECT group_set_id FROM group_sets
WHERE group_id IN
(SELECT group_id FROM membership
WHERE user_id = user_id_p)
GROUP BY group_set_id
HAVING count(*) = group_id_count_v
LOOP
-- make sure it has only the right number of group_ids
SELECT INTO count_v count(*) FROM group_sets
WHERE group_set_id = foo.group_set_id;
IF group_id_count_v = count_v THEN
RETURN foo.group_set_id;
END IF;
END LOOP;
RETURN -1;
END;
' LANGUAGE 'plpgsql';
----------------------------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Finding matching columns from two tables
@ 2002-02-21 22:34 Richard Emberson <emberson@phc.net>
2002-02-23 08:41 ` Re: Finding matching columns from two tables Masaru Sugawara <rk73@echna.ne.jp>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Emberson @ 2002-02-21 22:34 UTC (permalink / raw)
To: pgsql-sql
Below can be included in a psql via the \i command.
There are basically two tables (and some others for ref-int): membership
and group_sets.
A user (user_id) is a member of one or more groups (group_id). Groups
are also organized into
group sets (group_set_id) in the group_sets table. The task is to find
the group_set_id for a set
of groups, if it exists, given a user_id, and the associated groups that
user is a member of.
The function, get_group_set_id(), at the end is my attempt. It works
but its rather convoluted and
requires 4 SELECT statements.
Any help.
Thanks.
Richard
----------------------------------------------------------------
/*
In some cases, the group_ids associated with a group_set_id are the
same as the group_ids associated with a user_id. For a given user_id
determine if there is a corresponding group_set_id.
Find the group_set_id such that for a given user_id (input parameter)
there is a one-to-one correspondence between the group_ids associated
with the user_id and the group_ids associated with the group_set_id;
for every group_id that the user_id has, the group_set_id also has it
and for every group_id that the group_set_id has, the user_id also has
it.
If there is no such group_set_id, then return null.
What query will generate the group_set_id?
The function get_group_set_id() is my attempt. Is there a better way?
The fact that it has 4 SELECT statement in it is, I expect, far from
optimum.
SELECT get_group_set_id(10); -- should be 30
SELECT get_group_set_id(11); -- should be -1
SELECT get_group_set_id(12); -- should be -1
SELECT get_group_set_id(13); -- should be 31
*/
-- all of the users
-- user_id are unique
DROP TABLE users;
CREATE TABLE users (
user_id BIGINT,
-- other columns
PRIMARY KEY (user_id)
);
COPY users FROM stdin USING DELIMITERS ':';
10
11
12
13
\.
-- all of the groups
-- user_id are unique
DROP TABLE groups;
CREATE TABLE groups (
group_id BIGINT,
-- other columns
PRIMARY KEY (group_id)
);
COPY groups FROM stdin USING DELIMITERS ':';
20
21
22
23
24
\.
-- a user can be a member of one or more groups
DROP TABLE membership;
CREATE TABLE membership (
user_id BIGINT,
group_id BIGINT,
-- other columns
FOREIGN KEY (user_id) REFERENCES users (user_id),
FOREIGN KEY (group_id) REFERENCES groups (group_id)
);
COPY membership FROM stdin USING DELIMITERS ':';
10:20
10:21
11:20
12:21
12:22
12:23
13:20
13:22
\.
-- a group set is a set of one or more groups
DROP TABLE group_sets;
CREATE TABLE group_sets (
group_set_id BIGINT,
group_id BIGINT,
-- other columns
FOREIGN KEY (group_id) REFERENCES groups (group_id)
-- FOREIGN KEY (group_set_id) REFERENCES group_sets (group_set_id)
);
COPY group_sets FROM stdin USING DELIMITERS ':';
30:20
30:21
31:20
31:22
32:20
32:21
32:22
32:24
\.CREATE OR REPLACE FUNCTION get_group_set_id (
BIGINT
)
RETURNS BIGINT AS '
DECLARE
-- parameters
user_id_p ALIAS FOR $1;
-- local variables
group_id_count_v INTEGER;
count_v INTEGER;
group_set_id_v BIGINT;
foo group_sets%ROWTYPE;
BEGIN
-- how many groups were given to this user
SELECT INTO group_id_count_v count(*) FROM membership
WHERE user_id = user_id_p;
-- select all that have groups shared by the linked user and
-- have the right number (or more)
FOR foo IN SELECT group_set_id FROM group_sets
WHERE group_id IN
(SELECT group_id FROM membership
WHERE user_id = user_id_p)
GROUP BY group_set_id
HAVING count(*) = group_id_count_v
LOOP
-- make sure it has only the right number of group_ids
SELECT INTO count_v count(*) FROM group_sets
WHERE group_set_id = foo.group_set_id;
IF group_id_count_v = count_v THEN
RETURN foo.group_set_id;
END IF;
END LOOP;
RETURN -1;
END;
' LANGUAGE 'plpgsql';
----------------------------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Finding matching columns from two tables
2002-02-21 22:34 Finding matching columns from two tables Richard Emberson <emberson@phc.net>
@ 2002-02-23 08:41 ` Masaru Sugawara <rk73@echna.ne.jp>
0 siblings, 0 replies; 3+ messages in thread
From: Masaru Sugawara @ 2002-02-23 08:41 UTC (permalink / raw)
To: Richard Emberson <emberson@phc.net>; +Cc: pgsql-sql
On Thu, 21 Feb 2002 14:34:59 -0800
Richard Emberson <emberson@phc.net> wrote:
> Below can be included in a psql via the \i command.
I have lost sight of this mail -- especially detailed explanation annotated
on it. Judging from these examples, my previous queries wouldn't work correctly.
Anyway I have made several modifications on them.
select org2.group_set_id
from (select org0.group_id, org0.group_set_id
from organizations as org0
where exists
(select mem0.group_id
from membership as mem0
where org0.group_id = mem0.group_id
and mem0.user_id = <<input parameter>>
)
and not exists
(select org1.group_id, org1.group_set_id
from organizations as org1
where not exists
(select mem1.group_id
from membership as mem1
where org1.group_id = mem1.group_id
and mem1.user_id = <<input parameter>>
)
and org0.group_set_id = org1.group_set_id
)
) as org2
group by org2.group_set_id
;
Regards,
Masaru Sugawara
> ----------------------------------------------------------------
> /*
> In some cases, the group_ids associated with a group_set_id are the
> same as the group_ids associated with a user_id. For a given user_id
> determine if there is a corresponding group_set_id.
>
> Find the group_set_id such that for a given user_id (input parameter)
> there is a one-to-one correspondence between the group_ids associated
> with the user_id and the group_ids associated with the group_set_id;
> for every group_id that the user_id has, the group_set_id also has it
> and for every group_id that the group_set_id has, the user_id also has
> it.
> If there is no such group_set_id, then return null.
>
> What query will generate the group_set_id?
>
> The function get_group_set_id() is my attempt. Is there a better way?
> The fact that it has 4 SELECT statement in it is, I expect, far from
> optimum.
>
> SELECT get_group_set_id(10); -- should be 30
> SELECT get_group_set_id(11); -- should be -1
> SELECT get_group_set_id(12); -- should be -1
> SELECT get_group_set_id(13); -- should be 31
> */
>
> -- all of the users
> -- user_id are unique
> DROP TABLE users;
> CREATE TABLE users (
> user_id BIGINT,
> -- other columns
> PRIMARY KEY (user_id)
> );
> COPY users FROM stdin USING DELIMITERS ':';
> 10
> 11
> 12
> 13
> \.
>
> -- all of the groups
> -- user_id are unique
> DROP TABLE groups;
> CREATE TABLE groups (
> group_id BIGINT,
> -- other columns
> PRIMARY KEY (group_id)
> );
> COPY groups FROM stdin USING DELIMITERS ':';
> 20
> 21
> 22
> 23
> 24
> \.
> -- a user can be a member of one or more groups
> DROP TABLE membership;
> CREATE TABLE membership (
> user_id BIGINT,
> group_id BIGINT,
> -- other columns
> FOREIGN KEY (user_id) REFERENCES users (user_id),
> FOREIGN KEY (group_id) REFERENCES groups (group_id)
> );
> COPY membership FROM stdin USING DELIMITERS ':';
> 10:20
> 10:21
> 11:20
> 12:21
> 12:22
> 12:23
> 13:20
> 13:22
> \.
>
> -- a group set is a set of one or more groups
> DROP TABLE group_sets;
> CREATE TABLE group_sets (
> group_set_id BIGINT,
> group_id BIGINT,
> -- other columns
> FOREIGN KEY (group_id) REFERENCES groups (group_id)
> -- FOREIGN KEY (group_set_id) REFERENCES group_sets (group_set_id)
> );
> COPY group_sets FROM stdin USING DELIMITERS ':';
> 30:20
> 30:21
> 31:20
> 31:22
> 32:20
> 32:21
> 32:22
> 32:24
> \.CREATE OR REPLACE FUNCTION get_group_set_id (
> BIGINT
> )
> RETURNS BIGINT AS '
> DECLARE
> -- parameters
> user_id_p ALIAS FOR $1;
> -- local variables
> group_id_count_v INTEGER;
> count_v INTEGER;
> group_set_id_v BIGINT;
> foo group_sets%ROWTYPE;
> BEGIN
> -- how many groups were given to this user
> SELECT INTO group_id_count_v count(*) FROM membership
> WHERE user_id = user_id_p;
>
> -- select all that have groups shared by the linked user and
> -- have the right number (or more)
> FOR foo IN SELECT group_set_id FROM group_sets
> WHERE group_id IN
> (SELECT group_id FROM membership
> WHERE user_id = user_id_p)
> GROUP BY group_set_id
>
> HAVING count(*) = group_id_count_v
> LOOP
>
> -- make sure it has only the right number of group_ids
> SELECT INTO count_v count(*) FROM group_sets
> WHERE group_set_id = foo.group_set_id;
>
> IF group_id_count_v = count_v THEN
> RETURN foo.group_set_id;
> END IF;
>
> END LOOP;
>
> RETURN -1;
> END;
> ' LANGUAGE 'plpgsql';
> ----------------------------------------------------------------
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-02-23 08:41 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-21 20:17 Finding matching columns from two tables Richard Emberson <emberson@phc.net>
2002-02-21 22:34 Finding matching columns from two tables Richard Emberson <emberson@phc.net>
2002-02-23 08:41 ` Masaru Sugawara <rk73@echna.ne.jp>
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