List:General Discussion« Previous MessageNext Message »
From:Dirk Bremer Date:May 2 2006 3:06pm
Subject:RE: Finding duplicates, etc.
View as plain text  
> -----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  
Thread
Finding duplicates, etc.Dirk Bremer1 May
  • Re: Finding duplicates, etc.Peter Brawley1 May
RE: Finding duplicates, etc.Dirk Bremer2 May