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=20=
=20
> field given a value in another field?
> For example, I have a table that lists users and study names=20=20
> associated with those users. Each user can have one or more study=20=20
> names.

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

> =A0=A0=A0 =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0 Table "public.study"
> =A0=A0=A0 Column=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Type=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=20=20
> Modifiers=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0
> --------------+-----------------------------=20
> +------------------------------------------
> =A0sty_pk=A0=A0=A0=A0=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0 | not null default=20=20
> nextval('pk_seq'::text)
> =A0study_name=A0=A0 | character varying(128)=A0=A0=A0=A0=A0 | not null
> =A0start_date=A0=A0 | timestamp without time zone |
> =A0sty_comments | text=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 |
> =A0created_by=A0=A0 | integer=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0=A0 |
> 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