Thread: problem with join & count

Hi,

I don't succeed to build an SQL to do the task i describe below.
If someone can helps, thanks in advance.

I have two tables

number  | name
--------------
 1      | toto
 2      | titi
 3      | tutu

(here the number is a primary key)

and

number  |  tag
---------------
 1      | alpha
 1      | beta
 2      | gamma
 3      | zeta
 3      | epsilon

the number in the left column is the same of the number in the first table,
but can appear in that second table many times.

i need an sql query that will return:

name   |  how_often
-------------------
 toto  | 2
 titi  | 1
 tutu  | 2

that is, the result table should contain all names present in the first table
and then the number of times the number associated with the name appears in
the second table.

Thanks a lot for your help.

Patrick

/\//\/\/\\/\/\//\/\\/\/\\/\\/\//\/\\/\//\/\\/\//\/\\/\//\/\\
Patrick M.   pat@patoche.org    http://www.patoche.org/

> i need an sql query that will return:
>
> name   |  how_often
> -------------------
>  toto  | 2
>  titi  | 1
>  tutu  | 2
>
> that is, the result table should contain all names present in the first table
> and then the number of times the number associated with the name appears in
> the second table.

    Postgres  does not (and v6.5 will not) support outer joins or
    subselects in the targetlist, what's required to do that in a
    single SQL statement.

    What  you  could  do  is using a SQL function that covers the
    count() like this:

        create function num_refs (int4) returns int4 as '
            select count(*) from tab2 where id = $1;
        ' language 'SQL';

        select item_name, num_refs(id) as how_often from tab1;

    I changed 'number' into id and name  into  item_name  because
    both are reserved words.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #

Hm...

Why do be complex where you can be sample?

Your query is:

select name, count(*) as how_often
 from table1, table2
  where table1.number = table2.number
    group by name;

and that's all, folks!

P.S. Regarding your example, I can say that you are French! Isnt it? :))
"toto, titi" - very unique as names :))

Jan Wieck wrote:
>
> > i need an sql query that will return:
> >
> > name   |  how_often
> > -------------------
> >  toto  | 2
> >  titi  | 1
> >  tutu  | 2
> >
> > that is, the result table should contain all names present in the first table
> > and then the number of times the number associated with the name appears in
> > the second table.
>
>     Postgres  does not (and v6.5 will not) support outer joins or
>     subselects in the targetlist, what's required to do that in a
>     single SQL statement.
>
>     What  you  could  do  is using a SQL function that covers the
>     count() like this:
>
>         create function num_refs (int4) returns int4 as '
>             select count(*) from tab2 where id = $1;
>         ' language 'SQL';
>
>         select item_name, num_refs(id) as how_often from tab1;
>
>     I changed 'number' into id and name  into  item_name  because
>     both are reserved words.
>
> Jan
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #======================================== jwieck@debis.com (Jan Wieck) #

--
=====================================================
 Dimitri KRAVTCHUK  (dim)           Sun Microsystems
 Benchmark Engineer                 France
 dimitri@France.Sun.COM
=====================================================