I have the following SELECT that loads the clients if they are in any of the list items.

How would I re-write this to display if they exist in all 3 only? Subselect?

By chance, I am using SimpleQuery to allow users to create this Statement. Anyone have
experience on how to create what I mentioned above using the SQ Builder?

SELECT C.ClientID, C.DisplayName
FROM
    CLIENTS C LEFT OUTER JOIN  ClientRolodex CR ON (C.ClientID=CR.ClientID)
    INNER JOIN  Rolodex R ON (CR.RolodexID=R.RolodexID)
WHERE
(
UPPER(R.Description) in ('DRIP','ISP','REP PROSPECT')
)

Regards,
Scott

Scott,

<< How would I re-write this to display if they exist in all 3 only?
Subselect? >>

Actually INTERSECT should do the trick:

SELECT C.ClientID, C.DisplayName
FROM
    CLIENTS C LEFT OUTER JOIN  ClientRolodex CR ON (C.ClientID=CR.ClientID)
    INNER JOIN  Rolodex R ON (CR.RolodexID=R.RolodexID)
WHERE UPPER(R.Description)='DRIP'
INTERSECT
SELECT C.ClientID, C.DisplayName
FROM
    CLIENTS C LEFT OUTER JOIN  ClientRolodex CR ON (C.ClientID=CR.ClientID)
    INNER JOIN  Rolodex R ON (CR.RolodexID=R.RolodexID)
WHERE UPPER(R.Description)='ISP'
INTERSECT
SELECT C.ClientID, C.DisplayName
FROM
    CLIENTS C LEFT OUTER JOIN  ClientRolodex CR ON (C.ClientID=CR.ClientID)
    INNER JOIN  Rolodex R ON (CR.RolodexID=R.RolodexID)
WHERE UPPER(R.Description)='PROSPECT'

That should give you only the clients that exist in all 3 sets.

--
Tim Young
Elevate Software
www.elevatesoft.com