Re: Issue With Subqueries

2011-11-10 Thread Johan De Meersman 


- Original Message -
 From: Javier Yévenez jyeve...@gmail.com

 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:

No, he's using a subquery, those fields cannot be referenced.

--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Issue With Subqueries

2011-11-10 Thread Johan De Meersman 
- Original Message -
 From: Shawn Green (MySQL) shawn.l.gr...@oracle.com
 
 Simple math (set theory) suggests that all of the values of field1 on
 db2.table1 contain only copies or duplicates of the field1 values in
 the rows in db1.table1.

Shawn,

The select count(distinct)s show that db1.table1.field1 has a lot less unique 
values than db2.table1.field1, so I'm kind of struggling to understand how 3516 
unique values can be mapped to a subset of 1063 unique values :-)

 SELECT db2.field1, db1.field1
 FROM db2.table1
 LEFT JOIN db1.table1
ON db2.field1 = db1.field1
 WHERE db1.field1 IS NULL;

That's the interesting bit, indeed - if that gives back a more sensible number 
(logic dictates at least 2053 of the 3516 can not be the same as the 1063) 
it'll prove that there is indeed a bug somewhere.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Issue With Subqueries

2011-11-09 Thread Shawn Green (MySQL) 

Hi Mike,

On 11/8/2011 20:46, Mike Seda wrote:

All,
Can anyone out there explain the result of the third statement provided
below:

mysql select count(distinct field1) from db1.table1;
++
| count(distinct field1) |
++
| 1063 |
++
1 row in set (0.01 sec)

mysql select count(distinct field1) from db2.table1;
++
| count(distinct field1) |
++
| 3516 |
++
1 row in set (0.03 sec)

mysql select count(distinct field1) from db2.table1 where field1 not in
(select field1 from db1.table1);
++
| count(distinct field1) |
++
| 0 |
++
1 row in set (0.08 sec)

A colleague of mine is stating that the result should be much greater
than 0.

Please let me know what you think.

Thanks In Advance,
Mike



Simple math (set theory) suggests that all of the values of field1 on 
db2.table1 contain only copies or duplicates of the field1 values in the 
rows in db1.table1.


Try this:

SELECT db2.field1, db1.field1
FROM db2.table1
LEFT JOIN db1.table1
  ON db2.field1 = db1.field1
WHERE db1.field1 IS NULL;

How many rows do you get back from that?

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Issue With Subqueries

2011-11-09 Thread Javier Yévenez 
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

Re: Issue With Subqueries

2011-11-09 Thread Mike Seda 

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