[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] select and join
From:       Ulf Mehlig <umehlig () uni-bremen ! de>
Date:       1998-10-26 8:47:02
[Download RAW message or body]

Morelli 'ZioBudda' Davide Michel wrote:

> My question is: there is a way to join the table "utente" so to make a
> "select" that return me the column "utente.cognome",too ?  
> 
> i have make this try:
> 
> esame=> select utente.cognome, 
>                prestito.id_utente, 
>                libro.tipo,
>                count(*) 
>         from  prestito, libro 
>         where libro.id_libro = prestito.id_libro and 
>               utente.id_utente = prestito.id_utente 
>         group by id_utente, 
>                  tipo\g                   

(re-arranged)

Generally, the column functions/aggregates like count() can be applied
only if all non-aggregates/functions appear in the "group by"-section;
you joined the three tables correctly (however, you forgot to put the
third table name into the "from"-section!), but you have to tell the
database engine to determine the count grouped by "utente.cognome",
too. Try:

       select utente.cognome,                       
              prestito.id_utente,                   
              libro.tipo,                           
              count(*)                              
       from utente, prestito, libro
       where libro.id_libro = prestito.id_libro and 
             utente.id_utente = prestito.id_utente  
       group by utente.cognome, 
                prestito.id_utente, 
                libro.tipo

The result should be what you desired, because the relation
"utente.cognome"->"prestito.id_utente" is one-to-one. And you should
specify the table name for "id_utente" in the "group by"-section, too,
because this column name is not unique. Anyway, I did not test
anything, and maybe I overlooked something important ... I hope it
helps ... 

Yours, Ulf

-- 
======================================================================
 %%%%%            Ulf Mehlig              <ulf.mehlig@uni-bremen.de>
   %%%%!%%%       Projekt "MADAM"         <umehlig@uni-bremen.de>
%%%% %!% %%%%     ----------------------------------------------------
 ---| %%%         MADAM:  MAngrove    |  Center for Tropical Marine
    ||--%!%              Dynamics     |  Biology
    ||                  And           |  Fahrenheitstrasse 1
 _ /||\_/\_            Management     |  
/  /    \  \ ~~~~~~~~~~~~~~~~~        |  28359 Bremen/Germany
  ~~~~~~~~~~~~~~~~~~~~

[prev in list] [next in list] [prev in thread] [next in thread]