Re: Multiple duplicate addresses SQL help?

From: Bob Maggio <rmaggio_at_courts.state.ny.usNOSPAM>
Date: Fri, 24 Aug 2001 17:55:54 GMT
Message-ID: <3B8695BD.1EFCB26A@courts.state.ny.usNOSPAM>

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?

Billy Verreynne wrote:

> "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 - 12:55:54 CDT