Thread: Finding matching columns from two tables
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_vBIGINT; 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 SELECTgroup_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'; ----------------------------------------------------------------
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 annotatedon it. Judging from these examples, my previousqueries 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 membershipas mem1 where org1.group_id = mem1.group_id and mem1.user_id= <<input parameter>> ) and org0.group_set_id = org1.group_set_id ) ) as org2group 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'; > ---------------------------------------------------------------- >