Please help me with the following problem.

I have an item table with a Access Control List (ACL)
Column 1 -- Item ID
Column 2 -- ACL ID

In the ACL definition table, there are privileges:
Column 1 -- ACL ID
Column 2 -- Privilege ID
For a given ACL ID there is a list of allowed privileges.

I want to do a search that returns a result set that has
at most one row for an item and an indicator if the item
as one of a sepcified privilege.

Ex: Find the items based on some citeria and also find
if the item has Priv5, Priv8 and Priv20

I would like the result set to look like:

Item ID, Priv5 Indicator, Priv 8 Indicator, Priv 10 Indicator

I guess the Indicator could be 0 or 1. 0 if no privilege,
1 if the privilege is assigned to that item.

I don't want to have to post process the query looping through
the result set to check the privileges.

Thanks for any help!!

"George Hernando" <georgehernando (AT) hotmail (DOT) com> wrote

CASE/WHEN + GROUP BY + MAX (or MIN or SUM or COUNT)