Multiple duplicate addresses SQL help? 
Author Message
 Multiple duplicate addresses SQL help?

I need to pull out values from two tables, that contain mustiple address
duplicates.
The basic table structure is (without uneeded fields):
entity_address
entity_address_id
address_id
end_date

address
address_id
street1
street2
city ....etc

I need to select the addresses that are duplicates for each
entity_address, not having an end date of null (the only current
address)
So for each entity, I get all their entity_addresses. Of those, I need
to get ones that have the same street1, street2, city etc to
otheraddresses for that entity, except for the ones that have
end_date=null.
My problem is: How can I group together only the addresses that have
dupes?
I tried  a query that had
group by street1
having count(street1) > 1 but that didn't get me the desired results.
Any suggestion on how to grab these?
Thanks
 Bob Maggio



Tue, 10 Feb 2004 02:09:08 GMT
 Multiple duplicate addresses SQL help?


Quote:
> I need to pull out values from two tables, that contain mustiple address
> duplicates.

<snipped>

Quote:
> I need to select the addresses that are duplicates for each
> entity_address, not having an end date of null (the only current
> address)
> So for each entity, I get all their entity_addresses. Of those, I need
> to get ones that have the same street1, street2, city etc to
> otheraddresses for that entity, except for the ones that have
> end_date=null.
> My problem is: How can I group together only the addresses that have
> dupes?

Two methods I can think off.

SELECT
  entity_address,
  entity_address_id,
  address_id
FROM table1
WHERE end_date IS NULL
INTERSECT
SELECT
  entity_address,
  entity_address_id,
  address_id
FROM table2

This will give you a (distinct) result set of addresses that exist in table 1
(with a null end date criteria) and in table 2 (no criteria).

The other method is to join the two tables.
SELECT
 t1.entity_address,
 t1.entity_address_id,
 t1.address_id
FROM table1 t1,
     table2 t2
WHERE t1.entity_address = t2.entity_address,
AND   t1.entity_address_id = t2.entity_address_id
AND   t1.address_id = t2.address_id
AND   t1.end_date IS NULL

Which method to use depends largely on why you want to look for that duplicates.
If the intention is to delete them, I would use method 2 and only use the ROWID
in the SELECT part (called the projection part of a SQL SELECT in the old days).
This will then give me a list of rows to delete in the offending table.

If you need to know how many duplicates there are for example, method two can be
changed to include a GROUP BY and add a COUNT(*) to the projection.

--
Billy



Tue, 10 Feb 2004 15:27:57 GMT
 Multiple duplicate addresses SQL help?
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?
Quote:


> > I need to pull out values from two tables, that contain mustiple address
> > duplicates.
> <snipped>

> > I need to select the addresses that are duplicates for each
> > entity_address, not having an end date of null (the only current
> > address)
> > So for each entity, I get all their entity_addresses. Of those, I need
> > to get ones that have the same street1, street2, city etc to
> > otheraddresses for that entity, except for the ones that have
> > end_date=null.
> > My problem is: How can I group together only the addresses that have
> > dupes?

> Two methods I can think off.

> SELECT
>   entity_address,
>   entity_address_id,
>   address_id
> FROM table1
> WHERE end_date IS NULL
> INTERSECT
> SELECT
>   entity_address,
>   entity_address_id,
>   address_id
> FROM table2

> This will give you a (distinct) result set of addresses that exist in table 1
> (with a null end date criteria) and in table 2 (no criteria).

> The other method is to join the two tables.
> SELECT
>  t1.entity_address,
>  t1.entity_address_id,
>  t1.address_id
> FROM table1 t1,
>      table2 t2
> WHERE t1.entity_address = t2.entity_address,
> AND   t1.entity_address_id = t2.entity_address_id
> AND   t1.address_id = t2.address_id
> AND   t1.end_date IS NULL

> Which method to use depends largely on why you want to look for that duplicates.
> If the intention is to delete them, I would use method 2 and only use the ROWID
> in the SELECT part (called the projection part of a SQL SELECT in the old days).
> This will then give me a list of rows to delete in the offending table.

> If you need to know how many duplicates there are for example, method two can be
> changed to include a GROUP BY and add a COUNT(*) to the projection.

> --
> Billy



Wed, 11 Feb 2004 01:55:54 GMT
 Multiple duplicate addresses SQL help?

Quote:
>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



Wed, 11 Feb 2004 05:58:29 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. SQL 2000 Duplicate MAC addresses under NET Address Column in Enterprise Manager

2. 1 address table holding multiple entity addresses - how??

3. Multiple IP-Addresses/Multiple Listener

4. IIS/SQL Server Multiple IP address

5. Sql IP Listners on multiple IP Addresses

6. multiple sql group question on IP Address

7. Problem using multiple email addresses for an SQL Mail Operator

8. Oracle SQL*Net failing for multiple IP addresses

9. How to get SQL*Net to work on NT w/ multiple IP addresses

10. Address Duplicate Identification

11. Removing duplicate addresses from label print ?

12. email address duplicates


 
Powered by phpBB® Forum Software