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
[EMAIL PROTECTED]
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
