Re: Multiple duplicate addresses SQL help?

From: m.mueller <m.mueller_at_snafu.de>
Date: Fri, 24 Aug 2001 23:58:29 +0200
Message-ID: <3B86CE05.BE71614D@snafu.de>

>Billy, maybe I should try to bel clearer. I've had help from the web on this, but
>all answers seem to be miss my intent, probably becuase I am not as clear as I
>should be.
>What I need is this:
>In a table with a single field PK, (Address table -address_id PK) I need to find all
>the addresses (street, city etc) that are duplicated. But in the query, I need as
>part of the result set, the PK of Address Id so I can use it to query another table.
>Doing a thing like this:
>select a.STREET1 from address a, entity_address e
> where a.rowid <> ( select min(rowid)
> from address a2
> where a.street1 = a2.street1)
> and e.ADDRESS_ID = a.ADDRESS_ID
> and e.END_DATE is not null
> and e.COURT_ENTITY_ID = := v_court_entity_id
> group by a.STREET1
>gets me the dupes, just not the PK so I can reference them. Is there any way to get
>the dupes in that one table?

Hello Bob,
question one: Am I right with master-/detail relationship? If not, please post your table creation statements and forget about the rest! question two: If I got your relationship correctly, is this the solution you're searching for?
(gets you the duplicates for all rows in address_id and non-identical streets, city..)
technique is known as self-join.

create table entity_address(

       entity_address_id	number(5),
       address_id		number(5) CONSTRAINT pk_ent_add_address_id PRIMARY KEY,
       end_date			date

);

insert into entity_address values(1, 1, to_date(SYSDATE, 'YYYY-MM-DD'));
insert into entity_address values(2, 2, to_date(SYSDATE - 1, 'YYYY-MM-DD'));
insert into entity_address values(3, 3, to_date(SYSDATE - 7, 'YYYY-MM-DD'));

/*
table entity_address ok, no duplicates
*/

create table address(

       address_id number(5) constraint fk_address_id REFERENCES entity_address(address_id) ,

       street1			varchar2(10),
       street2			varchar2(10),
       city			varchAR2(10)

);

insert into address values(1, 'street1_a', 'street1_b', 'my_city1');
insert into address values(1, 'street2_a', 'street2_b', 'my_city2');
insert into address values(3, 'street3_a', 'street3_b', 'my_city3');

/*
duplicates in table adress, with table entity_address included */
select a1.address_id, a1.street1, a1.street2, a2.address_id, e.end_date, count(a1.address_id)
  from address a1, address a2, entity_address e

 where a1.address_id = a2.address_id
   and a1.street1 != a2.street1
   and a1.street2 != a2.street2

   and e.end_date is not null
group by a1.address_id, a1.street1, a1.street2, a2.address_id, e.end_date; --output:
ADDRESS_ID STREET1 STREET2 ADDRESS_ID END_DATE COUNT(A1.ADDRESS_ID)

---------- ---------- ---------- ---------- --------- --------------------
         1 street1_a  street1_b           1 01-AUG-17                    1
         1 street1_a  street1_b           1 01-AUG-23                    1
         1 street1_a  street1_b           1 01-AUG-24                    1
         1 street2_a  street2_b           1 01-AUG-17                    1
         1 street2_a  street2_b           1 01-AUG-23                    1
         1 street2_a  street2_b           1 01-AUG-24                    1
 

6 rows selected.  

/*
without the second table
*/
select a1.address_id, a1.street1, a1.street2, a2.address_id, count(a1.address_id)
  from address a1, address a2, entity_address e

 where a1.address_id = a2.address_id
   and a1.street1 != a2.street1
   and a1.street2 != a2.street2

group by a1.address_id, a1.street1, a1.street2, a2.address_id; --output:
ADDRESS_ID STREET1 STREET2 ADDRESS_ID COUNT(A1.ADDRESS_ID)

---------- ---------- ---------- ---------- --------------------
         1 street1_a  street1_b           1                    3
         1 street2_a  street2_b           1                    3

Regards
Manuela Mueller Received on Fri Aug 24 2001 - 16:58:29 CDT