Pavel Kutakov wrote:

> You should move user name condition to the join part. Here is a
> working query:
>
> SELECT DISTINCT
> D.RID,
> IIF((U.LOGIN_ID is not null), D.FYL_NMBR, '<Blocked>') AS FYL_NMBR,
> IIF((U.LOGIN_ID is not null), 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 AND U.LOGIN_ID =
> 'DAFFY_DUCK'
> ORDER BY D.RID

Unless the fields could actually contain NULL, this can be written even
simpler (at least I think it seems slightly simpler):

SELECT DISTINCT
D.RID,
COALESCE(D.FYL_NMBR, '<Blocked>') AS FYL_NMBR,
COALESCE(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
AND U.LOGIN_ID = 'DAFFY_DUCK'
ORDER BY D.RID

It may also be possible to remove the DISTINCT keyword, that is - unless
a document can be parts of two DOC_2_PRM_GRP that DAFFY DUCK is a member of.

Set