/* To describe my problem, you have to run segment one of my script,
shown below */


/* START OF SEGMENT ONE */

/* Table to hold the documents */
CREATE TABLE DOCS
(
RID INTEGER NOT NULL,
FYL_NMBR VARCHAR(25),
TITLE VARCHAR(50),
CONSTRAINT "PK_DOCS" PRIMARY KEY(RID)
);
COMMIT;

GRANT ALL ON DOCS TO PUBLIC;


/* Populate the document table */
INSERT INTO DOCS(RID, FYL_NMBR, TITLE) VALUES(1, 'B6-23', 'The secret
life of Teddy Bears');
INSERT INTO DOCS(RID, FYL_NMBR, TITLE) VALUES(2, 'A9-15', 'Horacio
Horn blower');
INSERT INTO DOCS(RID, FYL_NMBR, TITLE) VALUES(3, 'C3-12', 'Everything
you wanted to know about sqibbs');
INSERT INTO DOCS(RID, FYL_NMBR, TITLE) VALUES(4, 'D8-34', 'Vouge issue
17');
INSERT INTO DOCS(RID, FYL_NMBR, TITLE) VALUES(5, 'E7-22', 'BIRD FLU:
The next pandemic');
INSERT INTO DOCS(RID, FYL_NMBR, TITLE) VALUES(6, 'F2-11', 'Modern
Vaccination Weekly');
COMMIT;

/* Table to hold the permission groups */
CREATE TABLE PRM_GRPS
(
GRP_NM VARCHAR(25) NOT NULL,
CONSTRAINT "PK_PRM_GRPS" PRIMARY KEY(GRP_NM)
);
GRANT ALL ON PRM_GRPS TO PUBLIC;
COMMIT;


/* Populate the group table */
INSERT INTO PRM_GRPS(GRP_NM) VALUES('ENG');
INSERT INTO PRM_GRPS(GRP_NM) VALUES('LAB');
INSERT INTO PRM_GRPS(GRP_NM) VALUES('TECH');
COMMIT;

/* Create the user table */
CREATE TABLE USR
(
LOGIN_ID VARCHAR(25) NOT NULL,
CONSTRAINT "PK_USR" PRIMARY KEY(LOGIN_ID)
);
COMMIT;

GRANT ALL ON USR TO PUBLIC;
COMMIT;

/* Populate the user table */
INSERT INTO USR(LOGIN_ID) VALUES('DAFFY_DUCK');
INSERT INTO USR(LOGIN_ID) VALUES('FRED_FLINTSTONE');
INSERT INTO USR(LOGIN_ID) VALUES('MIGHTY_MOUSE');
INSERT INTO USR(LOGIN_ID) VALUES('DONALD_DUCK');
INSERT INTO USR(LOGIN_ID) VALUES('SNEEZY');
INSERT INTO USR(LOGIN_ID) VALUES('LAZY_BOY');
INSERT INTO USR(LOGIN_ID) VALUES('TED_NEELY');
INSERT INTO USR(LOGIN_ID) VALUES('HAGART');
COMMIT;

/* Create the user to group table */
CREATE TABLE USR_2_GRP
(
GRP_NM VARCHAR(25),
LOGIN_ID VARCHAR(25),
CONSTRAINT "PK_USR_2_GRP" PRIMARY KEY(GRP_NM, LOGIN_ID)
);
COMMIT;

ALTER TABLE USR_2_GRP ADD CONSTRAINT "FK1_USR_2_GRP" FOREIGN
KEY(GRP_NM) REFERENCES PRM_GRPS(GRP_NM) ON UPDATE CASCADE ON DELETE
CASCADE;
ALTER TABLE USR_2_GRP ADD CONSTRAINT "FK2_USR_2_GRP" FOREIGN
KEY(LOGIN_ID) REFERENCES USR(LOGIN_ID) ON UPDATE CASCADE ON DELETE
CASCADE;
COMMIT;

GRANT ALL ON USR_2_GRP TO PUBLIC;
COMMIT;

/* Populate the user to group table */
INSERT INTO USR_2_GRP(GRP_NM, LOGIN_ID) VALUES('ENG', 'DAFFY_DUCK');
INSERT INTO USR_2_GRP(GRP_NM, LOGIN_ID) VALUES('ENG', 'FRED_FLINTSTONE');
INSERT INTO USR_2_GRP(GRP_NM, LOGIN_ID) VALUES('ENG', 'MIGHTY_MOUSE');
INSERT INTO USR_2_GRP(GRP_NM, LOGIN_ID) VALUES('ENG', 'LAZY_BOY');
INSERT INTO USR_2_GRP(GRP_NM, LOGIN_ID) VALUES('ENG', 'DONALD_DUCK');
INSERT INTO USR_2_GRP(GRP_NM, LOGIN_ID) VALUES('ENG', 'SNEEZY');
INSERT INTO USR_2_GRP(GRP_NM, LOGIN_ID) VALUES('LAB', 'FRED_FLINTSTONE');
INSERT INTO USR_2_GRP(GRP_NM, LOGIN_ID) VALUES('LAB', 'TED_NEELY');
INSERT INTO USR_2_GRP(GRP_NM, LOGIN_ID) VALUES('LAB', 'SNEEZY');
INSERT INTO USR_2_GRP(GRP_NM, LOGIN_ID) VALUES('TECH', 'HAGART');
INSERT INTO USR_2_GRP(GRP_NM, LOGIN_ID) VALUES('ENG', 'HAGART');
COMMIT;


/* Create the document group permission table */
CREATE TABLE DOC_2_PRM_GRP
(
RID INTEGER NOT NULL,
GRP_NM VARCHAR(25) NOT NULL,
CONSTRAINT "PK_DOC_2_PRM_GRP" PRIMARY KEY(RID, GRP_NM),
CONSTRAINT "FK1_DOC_2_PRM_GRP" FOREIGN KEY(RID) REFERENCES DOCS(RID)
ON UPDATE CASCADE ON DELETE CASCADE
);
COMMIT;

GRANT ALL ON DOC_2_PRM_GRP TO PUBLIC;
COMMIT;

INSERT INTO DOC_2_PRM_GRP(RID, GRP_NM) VALUES(1, 'ENG');
INSERT INTO DOC_2_PRM_GRP(RID, GRP_NM) VALUES(2, 'ENG');
INSERT INTO DOC_2_PRM_GRP(RID, GRP_NM) VALUES(3, 'ENG');
INSERT INTO DOC_2_PRM_GRP(RID, GRP_NM) VALUES(4, 'TECH');
INSERT INTO DOC_2_PRM_GRP(RID, GRP_NM) VALUES(5, 'TECH');
INSERT INTO DOC_2_PRM_GRP(RID, GRP_NM) VALUES(6, 'LAB');
INSERT INTO DOC_2_PRM_GRP(RID, GRP_NM) VALUES(4, 'LAB');
COMMIT;

/* END OF SEGMENT ONE */



/* START OF SEGMENT TWO */


/* Now for the problem:
/* I have to show a grid that contains a row for each and every
document in the docs table, however */
/* in some of the rows, if the user is allowed to see the file number
and document title, by way of */
/* being a member of the permitted group, he can see the file number
and the document title in the */
/* grid row. If the user is not allowed to see this information,
then the user will see a grid, and */
/* each row will display the RID, but instead of showing the file
number, or document title, the grid */
/* cell will contain the string <Blocked> in each case. Here is the
closest I could come to solve this: */


SELECT DISTINCT
D.RID,
IIF((U.LOGIN_ID = 'DAFFY_DUCK'), D.FYL_NMBR, '<Blocked>') AS FYL_NMBR,
IIF((U.LOGIN_ID = 'DAFFY_DUCK'), D.TITLE, '<Blocked>') AS DOC_TYT
FROM
DOCS D
LEFT JOIN DOC_2_PRM_GRP G ON G.RID = D.RID
LEFT JOIN USR_2_GRP U ON U.GRP_NM = G.GRP_NM
ORDER BY D.RID

/* See that RIDs 1, 2 and 3 are listed twice, but 4,5 and 6 are
listed once (and correctly blocked) */

/* Note that I'm almost there, however, I don't want to show 2 rows
for the same document. If a row */
/* exists, that the user can see the information, then there's no
point in re-listing another row */
/* that says <Blocked>, however, as you can see, I DO want to list
the rows that the user does not have */
/* permission to see that file number and document title, and show
just the RID, then <Blocked>, <Blocked>*/

/* Thank you for any help you can provide! */

/* END OF SEGMENT TWO */