> -----Original Message-----
> From: Peter Brawley [mailto:peter.brawley@stripped]
> Sent: Monday, May 01, 2006 16:55
> To: Dirk Bremer
> Cc: mysql@stripped
> Subject: Re: Finding duplicates, etc.
>
> Dirk,
>
> >I would like to know where there ar duplicate accounts that
> do not have
> >duplicate addresses. I think that at this point in time that
> these are
> >included in the first query. I would like to separate these out to
> >report on them.
>
> How about ...
>
> select account,ident,address
> from group15034_i g1
> inner join group15034_i g2 using (account)
> where g1.address <> g2.address;
>
Peter,
You got me started on the right track. Here is what I ended up with that
seems to satisfy my requirements:
select distinct g1.account,g1.sub_account,g1.address from group15034 as
g1
inner join group15034 as g2 using (account)
where (g1.status = 'single')
and (g1.address <> g2.address)
order by account,sub_account;
The distinct clause had the most effect on limiting the results to a
manageable set.
Thanks for your help and if anyone has any suggestions to refine this
query, please let me know.
Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO
- USA Central Time Zone
636-755-2652 fax 636-755-2503
dirk.bremer@stripped
www.nisc.coop
