Re: Need a distinct list

You can use distinct option.
In your query, table ROLE is useless.

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

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

Regards.

Jim Poe a écrit dans le message <37C32CF9.F1859F62_at_fulcrumit.com>...
>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 Wed Aug 25 1999 - 02:38:59 CDT