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
