Re: query assistance

Hi Jodi,

On Wednesday, November 5, 2003, at 12:16 AM, Jodi Kanter wrote:

> Is there a straight forward way to pull out duplicates in a particular
> field given a value in another field?
> For example, I have a table that lists users and study names
> associated with those users. Each user can have one or more study
> names.

If I understand you correctly, this is the table you're interested in.
Your public.study table doesn't include any users as far as I can tell
(though please correct me if I'm misunderstanding you).

>                                    Table "public.study"
>     Column    |            Type             |               
> Modifiers                
> --------------+-----------------------------
> +------------------------------------------
>  sty_pk       | integer                     | not null default
> nextval('pk_seq'::text)
>  study_name   | character varying(128)      | not null
>  start_date   | timestamp without time zone |
>  sty_comments | text                        |
>  created_by   | integer                     |
> Indexes: study_pkey primary key btree (sty_pk)

I think something like this is what you're looking for:

SELECT user, study_name, COUNT(*)
FROM <table linking user and study_name>
GROUP BY user, study_name
HAVING COUNT(*) > 1;

where the FROM clause lists the table linking users and study_names.

Does this help?

Michael
grzm myrealbox com