From: Peter Brawley
Date: May 1 2006 9:54pm
Subject: Re: Finding duplicates, etc.
List-Archive: http://lists.mysql.com/mysql/197487
Message-Id: <4456839A.4010204@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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;

PB

-----

Dirk Bremer wrote:
> I have the following table:
>
> *Column Information For - production.group15034_I*/
> ----------------------------------------------------
>
> Field        Type                       Collation          Null    Key
> Default     Extra           Privileges                       Comment
> -----------  -------------------------  -----------------  ------
> ------  ----------  --------------  -------------------------------
> -------
> ident        int(11)                    NULL                       PRI
> (NULL)      auto_increment  select,insert,update,references         
> account      int(10) unsigned zerofill  NULL
> 0000000000                  select,insert,update,references         
> sub_account  tinyint(3) unsigned        NULL
> 0                           select,insert,update,references         
> address      varchar(132)               latin1_swedish_ci
> select,insert,update,references         
> data         text                       latin1_swedish_ci
> select,insert,update,references         
>
> /*Index Information For - production.group15034_I*/
> ---------------------------------------------------
>
> Table         Non_unique  Key_name  Seq_in_index  Column_name  Collation
> Cardinality  Sub_part  Packed  Null    Index_type  Comment
> ------------  ----------  --------  ------------  -----------  ---------
> -----------  --------  ------  ------  ----------  -------
> group15034_I           0  PRIMARY              1  ident        A
> 0    (NULL)  (NULL)          BTREE              
>
> /*DDL Information For - production.group15034_I*/
> -------------------------------------------------
>
> Table         Create Table
>
> ------------
> ----------------------------------------------------------------------
> group15034_I  CREATE TABLE `group15034_I` (
>
>                 `ident` int(11) NOT NULL auto_increment,
>
>                 `account` int(10) unsigned zerofill NOT NULL default
> '0000000000',  
>                 `sub_account` tinyint(3) unsigned NOT NULL default '0',
>
>                 `address` varchar(132) NOT NULL default '',
>
>                 `data` text NOT NULL,
>
>                 PRIMARY KEY  (`ident`)
>
>               ) ENGINE=MyISAM DEFAULT CHARSET=latin1
>
>
> The account and address fields will contain duplicate values under
> certain scenarios. I have the following 3 requirements:
>
> 1. Select the accounts that have unique accounts and addresses, i.e. not
> duplicate:
> select ident,account,address,count(*) as N,data from group15034_I group
> by account,address having N = 1
> This select appears to work fine.
>
> 2. Select the acocunts that have both duplicate accounts and addresses:
> select account,address,count(*) as N from group15034_I group by
> account,address having N > 1
> This select appears to work fine.
>
> 3. I want the exceptions to the above two conditions. Specifically, 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.
>
> I can't figure out a way to accomplish number 3 but know that in the
> given data set that this condition does occur.
>
> 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
>
>   


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.5.1/327 - Release Date: 4/28/2006