RE: Can't put sub-queries values in queries results?

Hello Andrew,

On 22-Jul-00 02:42:17, you wrote:

>> I want to look in a table and count how many rows of other table
>> have a given
>> field that matches the value of the first table. I don't want to
>> join because
>> if there are no matches for a given value of the first table, the query
>> does not return me any results for that value.
>>
>> For instance I have a table t1 with field f1 and table t2 with field f2.
>>
>> t1.f1
>> 0
>> 1
>> 2
>>
>> t2.f2
>> 0
>> 0
>> 1
>>
>> I want the result to be:
>>
>> f1 | my_count
>> ---+---------
>> 0 | 2
>> 1 | 1
>> 2 | 0
>>
>> so I do
>>
>> SELECT f1, (SELECT COUNT(*) FROM t2 WHERE t2.f2=t1.f1) AS my_count FROM t1

>What about this:

>SELECT f1, COUNT(f2) FROM t1, t2 WHERE f1=f2 GROUP BY f1

>or something along those lines.

As I mentioned joins would suppress values of t1 that does not exist in t2. In this
case it would return only.

f1 | my_count
---+---------
0 | 2
1 | 1

Try this and you will see:

DROP TABLE t1;
DROP TABLE t2;
CREATE TABLE t1 (f1 INT);
INSERT INTO t1 (f1) VALUES (0);
INSERT INTO t1 (f1) VALUES (1);
INSERT INTO t1 (f1) VALUES (2);
CREATE TABLE t2 (f2 INT);
INSERT INTO t2 (f2) VALUES (0);
INSERT INTO t2 (f2) VALUES (0);
INSERT INTO t2 (f2) VALUES (1);
SELECT f1, COUNT(f2) FROM t1, t2 WHERE f1=f2 GROUP BY f1;

Regards,
Manuel Lemos

Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos(at)acm(dot)org
--
E-mail: mlemos(at)acm(dot)org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--