Hello,
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
PostgreSQL does not seem to understand this. I wonder if this is a
limitation or I am doing something wrong.
If I can't do what I want this way, I wonder if is there some other way to
do it besides making two queries by passing the values from one to the
other.
Regards,
Manuel Lemos
Web Programming Components using PHP Classes.
Look at: http://phpclasses.UpperDesign.com/?user=mlemos@acm.org
--
E-mail: mlemos@acm.org
URL: http://www.mlemos.e-na.net/
PGP key: http://www.mlemos.e-na.net/ManuelLemos.pgp
--
