Need a distinct list

I have an OFFICE table, a ROLE table, and a CTAC table. A single CTAC can have multiple ROLEs in a single OFFICE. If I know the OFFICE_ID, how do I get a list of CTAC's where an individual contact will only appear once even if they have multiple roles?

I use an OFFICE_K_ROLE table to track multiple roles for an office and a CTAC_K_ROLE table to track multiple roles for a CTAC.

This select statement returns repeating CTAC records:

SELECT D.*,( D.NAME_FIRST || ' ' || D.NAME_LAST ) FULLNAME

                 FROM OFFICE_K_ROLE A, ROLE B, CTAC_K_ROLE C, CTAC D
                 WHERE A.OFFICE_ID=:OFFICE_ID AND
                                  B.ROLE_ID=A.ROLE_ID AND 
                                  C.ROLE_ID=B.ROLE_ID AND
                                  D.CTAC_ID=C.CTAC_ID
                 ORDER BY D.NAME_LAST,D.NAME_FIRST;

--
Jim Poe
<jpoe_at_fulcrumit.com>
Fulcrum InteTech, Inc. Received on Tue Aug 24 1999 - 18:38:22 CDT