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

Manuel Lemos wrote:
>
> 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.

What about defining a function for the sub-query:

CREATE FUNCTION count_subs( INT4 ) RETURNS INT4 AS '
SELECT COUNT(*) FROM t2 WHERE t2.f2=$1;
' LANGUAGE 'SQL';

Then you should be able to:

SELECT f1, count_subs(f1) FROM t1;

Hope that helps,
                Andrew.
--
_____________________________________________________________________
            Andrew McMillan, e-mail: Andrew@cat-it.co.nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267