Re: Query

In article <8uij1c$mju$1_at_news.doit.wisc.edu>,   derrian.jones_at_doit.wisc.edu (Derrian Jones) wrote:
> Hello all. I've got a question about a query I need written.
>
> The set up...
>
> Table A: has several columns, but the most important of which are
 Person_ID (a
> unique identifier that is the key to Table A) and Employee_ID (a non-
 unique,
> at least to this table, number).
>
> Table B is a Child to Parent Table A. The key to Table B is merely a
> sequential number, but the foreign key to the table is Employee_ID.
 A field
> on Table B is ID_Number. It is non-unique.
>
> What I need to do is spin through Table B, finding ALL values of
 ID_Number
> that are ALIKE. Then, for those like ID_Numbers, I need to take the
> corresponding Employee_IDs and read Table A for each. Finally, I
 need to
> compare the corresponding Person_IDs for each of those Employee_IDs
 and, if
> they are DIFFERENT, display them as a pair (or group).
>
> The goal, then, is to list all the Person_IDs from Table A that share
 the same
> ID_Number from Table B.
>
> Note that Table A and B each contain about 100,000 rows of data.
>
> Can anyone help me?
>
> Thanks,
>
> ice
>
>

select person_id, id_number
from table_a
, (select id_number

   from table_b
   group by id_number
   having count(*) > 1
  ) b
where table_a.id_number = b.id_number
and person_id <> id_number
/

Hth,

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com http://www.deja.com/
Before you buy.