I am new to subselects, and what I really want is to do them in an update, but the following shows a simpler select, that also doesn't seem to work as I think it should. Advice? Do I just misunderstand how subselects are actually executed?
(This is on mysql version 4.1.0-alpha-max-nt.) First, here's the tables in question: mysql> describe m; +-------+---------+-----------+------+-----+---------+-------+ | Field | Type | Collation | Null | Key | Default | Extra | +-------+---------+-----------+------+-----+---------+-------+ | toid | int(11) | binary | YES | | NULL | | | rd | int(11) | binary | YES | | NULL | | +-------+---------+-----------+------+-----+---------+-------+ mysql> describe p; +---------+---------+-----------+------+-----+---------+-------+ | Field | Type | Collation | Null | Key | Default | Extra | +---------+---------+-----------+------+-----+---------+-------+ | userid | int(11) | binary | | PRI | 0 | | | pmnew | int(11) | binary | YES | | NULL | | | pmtotal | int(11) | binary | YES | | NULL | | +---------+---------+-----------+------+-----+---------+-------+ and the data in the tables... mysql> select * from p; +--------+-------+---------+ | userid | pmnew | pmtotal | +--------+-------+---------+ | 1 | 0 | 0 | | 2 | 0 | 0 | +--------+-------+---------+ 2 rows in set (0.00 sec) mysql> select * from m; +------+-------+ | toid | rd | +------+-------+ | 1 | 0 | | 1 | 0 | | 1 | 0 | | 1 | 12 | | 1 | 15 | | 1 | 123 | | 1 | 12312 | | 1 | 12312 | | 1 | 123 | | 2 | 0 | | 2 | 0 | | 2 | 1 | | 2 | 2 | +------+-------+ 13 rows in set (0.00 sec) mysql> select userid,pmtotal,pmnew, (select count(rd) from m where toid=p.userid) calc_total, (select count(rd) from m where rd=0 and toid=p.userid) calc_new from p where userid in (select distinct toid from m); +--------+---------+-------+------------+----------+ | userid | pmtotal | pmnew | calc_total | calc_new | +--------+---------+-------+------------+----------+ | 1 | 0 | 0 | 9 | 3 | | 2 | 0 | 0 | NULL | NULL | +--------+---------+-------+------------+----------+ Now, the first row has what I want and expect, in calc_total and calc_new... but the second row doesn't. Why? Shouldn't the subselects in the field selector part (not the where part) be "re-executed" for each value in the IN()? If so, I'd expect calc_total to be '4', and calc_new to be '2' for the second row. For example, if I manually fudge the WHERE ... IN, I get: mysql> select userid,pmtotal,pmnew, (select count(rd) from m where toid=p.userid) calc_total, (select count(rd) from m where rd=0 and toid=p.userid) calc_new from p where userid in (2); +--------+---------+-------+------------+----------+ | userid | pmtotal | pmnew | calc_total | calc_new | +--------+---------+-------+------------+----------+ | 2 | 0 | 0 | 4 | 2 | +--------+---------+-------+------------+----------+ which is exactly what I want, but all at once :-) Ideas? Misunderstanding on my part? Bug? (By the way, what I eventually want to do is an update to set pmtotal and pmnew to be the calc_total and calc_new; in the real schema this is a simplified version of, they are essentially 'caches' of the new/total counts...) Any comments appreciated. Ben Margolin ===== [ Ben Margolin -- [EMAIL PROTECTED] -- [EMAIL PROTECTED] ] __________________________________ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
