Cannot find the correct SQL statement

From: Michael G. Schneider <mgs_at_mgs-software.de>
Date: Sat, 6 Jan 2001 09:10:00 +0100
Message-ID: <936jfd$roj$03$1@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 - 02:10:00 CST