These are not real subselects. You speak of a subselect when you are
define a select in the FROM clause of a kwiri. If you define them in the
WHERE clause, you speak of derived tables. Putting them even into the
SELECT clause is something specific to MySQL (as far as I know).
Ok, now this is theory and does not help you much :) Thus you can solve
this without subselects:
SELECT userid, pmtotal, pmnew,
count(*) calc_total,
sum(if(rd=0,1,0) calc_new
FROM m,
p
WHERE userid = toid
GROUP BY userid
OR ( if you insist on derived tables and subselects )
SELECT userid, pmtotal, pmnew,
m2.calc_total calc_total,
m1.calc_new calc_new
FROM m,
p,
( SELECT toid, count(*) calc_new
FROM m
WHERE rd = 0
GROUP BY toid ) m1,
( SELECT toid, count(*) calc_total
FROM m
GROUP BY toid ) m2
WHERE userid IN ( SELECT distinct toid
FROM m )
AND p.userid = m2.toid
AND p.userid = m1.toid
But I would use the first one...
/rudy
-----Original Message-----
From: Ben Margolin [mailto:[EMAIL PROTECTED]
Sent: woensdag 16 juli 2003 1:36
To: [EMAIL PROTECTED]
Subject: subselect question... shouldn't this work?
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
