On 20/05/2016 11:49 AM, Tom Krieg wrote:
> This has me beat. I have 2 tables, address_detail and address_locality.
> fields in address_detail include a PKey = address_detail_uid (int),
> locality_uid (int), address_postcode (character(4)) and other stuff.
>
> address_locality including a PKey = locality_uid (integer).
>
> There is a foreign key in address_detail linked --> locality_uid.
>
> There are no address_detail rows with a NULL locality_uid, so each
> locality in address_detail has a matching locality record.
>
> My task is to transfer postcode from address detail to locality. There
> are many address details with the same locality and postcode.
>
> Step 1: I get the number of distinct localities in address_detail.
>
> SELECT DISTINCT locality_uid FROM address_detail;
>
> 15,195 localities
>
> Step 2: I get the number of localities in the locality table
>
> SELECT locality_uid from locality;
>
> 16,398 localities
>
> OK, so there are a number of localities which don't have addresses.
> That's OK because there are a lot of parks, fields, beaches etc etc in
> there as localities.
>
> Step 3: I now get the number of distinct locality/postcode
>         combinations in the address table, to see if the total
>         localities match.
>
> SELECT DISTINCT locality_uid, address_postcode FROM address_detail;
>
> 17,824 rows
>
> So we have a number of localities with more than 1 postcode. Now I want
> to determine which ones.
>
> Step 4: Check for duplicate postcodes.
>
> SELECT foo.address_postcode, foo.locality_uid, COUNT(1) as COUNT
> FROM
>    (SELECT DISTINCT address_detail.address_postcode,
>                     address_detail.locality_uid
>     FROM address_detail) AS foo
> GROUP BY foo.address_postcode, foo.locality_uid
> HAVING COUNT(1) > 1
> ;
>
> 0 Rows
>
> Can someone tell me where are the duplicates?
>
>
>
select Locality_uid,count(DISTINCT address_postcode)
FROM address_detail
GROUP BY locality_uid
HAVING count(DISTINCT address_postcode) > 1