Re: Finding matching columns from two tables
От | Masaru Sugawara |
---|---|
Тема | Re: Finding matching columns from two tables |
Дата | |
Msg-id | 20020223173857.DE5D.RK73@echna.ne.jp обсуждение исходный текст |
Ответ на | Finding matching columns from two tables (Richard Emberson <emberson@phc.net>) |
Список | 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 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'; > ---------------------------------------------------------------- >
В списке pgsql-sql по дате отправления: