Re: Multiple duplicate addresses SQL help?

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Fri, 24 Aug 2001 09:27:57 +0200
Message-ID: <9m4vpa$3ui$1@ctb-nnrp1.saix.net>

"Bob Maggio" <rmaggio_at_courts.state.ny.usNOSPAM> wrote

> 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

Received on Fri Aug 24 2001 - 02:27:57 CDT