Rong,
On the server, we are using the following RPMs (from MySQL):
- MySQL-server-5.5.11-1.rhel5.x86_64
- MySQL-client-5.5.11-1.rhel5.x86_64
On the clients, we are using the following RPM (from Red Hat):
- mysql-5.0.45-7.el5.x86_64 RPM
Mike
On 11/09/2011 10:49 AM, Rong Chen wrote:
Javier,
I tried it, and still get the result of 0. It is a bug in the mysql.
Mike, could you post the version of mysql?
Thank you.
Rong
*/Rong Chen, Ph.D./*
/Bioinformatics Scientist, Butte Lab/
/Division of Systems Medicine, MC5415/
/Dept. of Pediatrics/
/MSOB X155/
/1265 Welch Road/
/Stanford University/
/Stanford, CA 94305/
/Tel: 858-837-2265 (cell)/
/Fax: 650-724-2259/
/Twitter: @RongChenBioinfo <http://twitter.com/#%21/RongChenBioinfo>/
/http://www.stanford.edu/~rchen1 <http://www.stanford.edu/%7Erchen1>/
*From:*Javier Yévenez [mailto:jyeve...@gmail.com]
*Sent:* Wednesday, November 09, 2011 7:19 AM
*To:* Shawn Green (MySQL)
*Cc:* Mike Seda; mysql@lists.mysql.com; Rong Chen
*Subject:* Re: Issue With Subqueries
Hi,
mysql> select count(distinct field1) from db2.table1 where
field1 not in
(select field1 from db1.table1);
+------------------------+
| count(distinct field1) |
+------------------------+
If the field db1.table1.field1 has the same name that the field
db2.table1.field1, maybe you have to use an alias for each table:
try:
mysql> select count(distinct A.field1) from db2.table1 A where field1
not in (select B.field1 from db1.table1 B);
--
Javier