Обсуждение: Matching columns in rows from two tables
I have the following tables: -- all of the users -- user_id are unique CREATE TABLE users ( user_id BIGINT, .... PRIMARY KEY (user_id) ); -- all of the groups -- user_id are unique CREATE TABLE groups ( group_id BIGINT, ... PRIMARY KEY (group_id) ); -- all of the group sets -- group_set_id are unique CREATE TABLE group_sets ( group_set_id BIGINT, ... PRIMARY KEY (group_set_id) ); -- a user can be a member of one or more groups CREATE TABLE membership ( user_id BIGINT, group_id BIGINT, .... FOREIGN KEY (user_id) REFERENCESusers (user_id), FOREIGN KEY (group_id) REFERENCES groups (group_id) ); -- a group set is a set of one or more groups CREATE TABLE group_sets ( group_id BIGINT, group_set_id BIGINT, .... FOREIGN KEY (group_id) REFERENCESgroups (group_id), FOREIGN KEY (group_set_id) REFERENCES group_sets (group_set_id) ); I want to: 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? There ought to be some combination of joins, intersections, etc. that can generate the result but I am getting hungup on the fact that the number of group_ids being matched is not fixed. Thanks for any help. Richard
On Wed, 20 Feb 2002 13:51:12 -0800 Richard Emberson <emberson@phc.net> wrote: > I have the following tables: > ... > -- a group set is a set of one or more groups > CREATE TABLE group_sets ( Group_sets is already defined, isn't it ? > group_id BIGINT, > group_set_id BIGINT, > .... > FOREIGN KEY (group_id) REFERENCES groups (group_id), > FOREIGN KEY (group_set_id) REFERENCES group_sets (group_set_id) > ); > > I want to: > 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? I'm not sure whether or not I have understood what you described. If I use organizations instead of the 2nd group_sets you were created, presumably ... -- using no joins select org1.group_set_id from organizations as org1where exists (select org0.group_id from organizations asorg0 where exists (select mem.group_id from membership as mem where org0.group_id = mem.group_id and user_id = <<input parameter>> group by mem.group_id having count(*) = 1 ) and org1.group_id = org0.group_id group by org0.group_id having count(*)= 1 ) ; -- using inner joins select org2.group_set_id from (select org0.group_id from (select group_id from membership where user_id= <<input parameter>> group by group_id having count(*) = 1 ) as mem inner join organizations as org0 on (mem.group_id = org0.group_id) group by org0.group_id having count(*) = 1 ) as org1 inner join organizations as org2 on (org1.group_id = org2.group_id) ; Regards, Masaru Sugawara
On Sat, 23 Feb 2002 08:03:19 -0800 Richard Emberson <emberson@phc.net> wrote: > Thank you very much for your help. I managed to create a query with only two select > statements > and none of the selects are inner-selects: > > SELECT group_set_id, count(*) FROM group_sets , membership > WHERE membership.user_id = <<input parameter>> > AND membership.group_id = group_sets.group_id > GROUP BY group_set_id > INTERSECT > SELECT group_set_id, count(*) FROM group_sets > GROUP BY group_set_id; > > There may still be a better (better performance) query, Quite so. I would think it's probably one of the best way. Practically,I found my query including "where exists/not exists"is awfully slow if thereare about 1000+ rows. > but, again, this is the > best I could > come up with. > > Thanks. > > Richard > > > On Wed, 20 Feb 2002 13:51:12 -0800 > > Richard Emberson <emberson@phc.net> wrote: > > > > > I have the following tables: > > > > > > > ... > > > > > -- a group set is a set of one or more groups > > > CREATE TABLE group_sets ( Regards, Masaru Sugawara