2010/1/19 Andreas <maps...@gmx.net> > Hi, > > I need something like the user-roles of PG to store options of my users. > I guess i need a table with roles, options and one that stores the > refernces from roles to options. > > roles (role_id, role_name) > option (option_id, option_name) > role_has_option (role_fk, option_fk) > > so far is easy. Now I can let role1 have option1 and option2 ... > > But I'd further like to let role2 inherit role1's options and also have > option3. > role_inherits_role (parent_role_fk, child_role_fk) > 1, 2 > > What SELECT would deliver all options for role2 inkluding the inherited > ones? > like > role_id, option_id > 2, 1 > 2, 2 > 2, 3 >
select role_fk as role_id, option_fk as option_id from role_has_option where role_fk = 2 union select inh.child_role_fk, opt.option_fk from role_has_option opt join role_inherits_role inh on inh.parent_role_fk = opt.role_fk where inh.child_role_fk = 2 ? > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
