At 11:04 PM 1/04/2003 +0000, you wrote:

>I have a table named Media with several columns populated as follows:
>
>MKey MAction MStoreId
>1 2 4
>2 1 4
>3 1 6
>
>I need to get a result set from this table only when all items for a
>particular MStoreId are in the MAction = 2 state. However, I don't
>have any input data. So I don't know the store id's.
>
>Can anyone suggest a simple why to get this result set?

>Kind of like saying: (pseudo sql)
>Select all items where the MStoreId is the same and MAction state is
>also 2.

select MKey, MAction, MStoreID from Media
group by MStoreID, MKey, MAction
having MAction = 2

> I have also tried:
> SELECT * FROM Media WHERE MAction = 2
> AND SINGULAR (SELECT * FROM Media WHERE MAction = 1)

> Which seems to work but I don't know why? What is this SINGULAR
> clause?

It's coincidental if SINGULAR() happened to return the result you were
looking for. It is a predicate, similar to EXISTS(), except that it causes
the query to return a set of one row, if and only if the subquery
specification resolves to a single output row ("singleton select"); if not
(i.e. either no eligible rows, or more than one eligible row), the query
will return nothing.

heLen