Re: Cannot find the correct SQL statement

From: Spencer <spencerp_at_swbell.net>
Date: Sat, 6 Jan 2001 09:20:06 -0600
Message-ID: <iHG56.992$sy.220631@nnrp1.sbc.net>

I'm thinking that an SQL statement like this may return the result set you are looking for:

SELECT ui.user

     , ui.item
     , SIGN(SUM(ui.sa)) as allowed
  FROM ( SELECT u.u  As user
              , i.i  As item
              , 0    As allowed
           FROM T_Item i
              , T_User u
         UNION ALL
         SELECT m.mu As user
              , s.si As item
              , s.sa As allowed
           FROM T_Membership m
              , T_Security s
          WHERE m.mg = s.sg
       ) ui

 GROUP BY ui.user, ui.item

"Michael G. Schneider" <mgs_at_mgs-software.de> wrote in message news:936jfd$roj$03$1_at_news.t-online.com...
> Suppose we have the following tables
>
> T_Item
> T_User
> T_Group
> T_Membership
> T_Security
>
> T_Item, T_User and T_Group are basic tables. T_Membership defines, which
> users are members of which groups. A user can be the member of several
> groups. T_Security defines which group may access a certain item.
>
> Now I would like to have the cartesian product of T_Item and T_User,
 telling
> me whether the user has access to the item or not. If there are several
> definitions for one pair <item,user> the definition "is allowed" should
 win.
>
> Can this be done with SQL?
>
> Michael G. Schneider
> mailto:mgs_at_mgs-software.de
>
>
>
> CREATE TABLE T_Item (i CHAR(10));
> CREATE TABLE T_User (u CHAR(10));
> CREATE TABLE T_Group (g CHAR(10));
> CREATE TABLE T_Membership (mg CHAR(10), mu CHAR(10));
> CREATE TABLE T_Security (si CHAR(10), sg CHAR(10), sa NUMBER(1,0));
>
> INSERT INTO T_Item VALUES('I1');
> INSERT INTO T_Item VALUES('I2');
> INSERT INTO T_Item VALUES('I3');
>
> INSERT INTO T_User VALUES('U1');
> INSERT INTO T_User VALUES('U2');
> INSERT INTO T_User VALUES('U3');
>
> INSERT INTO T_Group VALUES('G1');
> INSERT INTO T_Group VALUES('G2');
> INSERT INTO T_Group VALUES('G3');
>
> INSERT INTO T_Membership VALUES('G1','U1');
> INSERT INTO T_Membership VALUES('G1','U2');
> INSERT INTO T_Membership VALUES('G2','U2');
> INSERT INTO T_Membership VALUES('G3','U2');
> INSERT INTO T_Membership VALUES('G3','U3');
>
> INSERT INTO T_Security VALUES('I1','G1',0);
> INSERT INTO T_Security VALUES('I1','G2',1);
> INSERT INTO T_Security VALUES('I2','G1',1);
> INSERT INTO T_Security VALUES('I2','G3',1);
> INSERT INTO T_Security VALUES('I3','G1',0);
> INSERT INTO T_Security VALUES('I3','G3',1);
>
> SELECT * FROM T_Item;
> SELECT * FROM T_User;
> SELECT * FROM T_Group;
> SELECT * FROM T_Membership;
> SELECT * FROM T_Security;
>
>
> T_Item
> ------------------------
> I1
> I2
> I3
>
> T_User
> ------------------------
> U1
> U2
> U3
>
> T_Group
> ------------------------
> G1
> G2
> G3
>
> T_Membership
> ------------------------
> G1 U1
> G1 U2
> G2 U2
> G3 U2
> G3 U3
>
> T_Security
> ------------------------
> I1 G1 0
> I1 G2 1
> I2 G1 1
> I2 G3 1
> I3 G1 0
> I3 G3 1
>
>
>
Received on Sat Jan 06 2001 - 09:20:06 CST