From: | Ulf Mehlig <umehlig(at)uni-bremen(dot)de> |
---|---|
To: | michel(at)michel(dot)enter(dot)it |
Cc: | pgsql-general(at)postgreSQL(dot)org |
Subject: | Re: [GENERAL] select and join |
Date: | 1998-10-26 08:47:02 |
Message-ID: | 199810260847.JAA02224@uni-bremen.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)mehlig(at)uni-bremen(dot)de>
%%%%!%%% Projekt "MADAM" <umehlig(at)uni-bremen(dot)de>
%%%% %!% %%%% ----------------------------------------------------
---| %%% MADAM: MAngrove | Center for Tropical Marine
||--%!% Dynamics | Biology
|| And | Fahrenheitstrasse 1
_ /||\_/\_ Management |
/ / \ \ ~~~~~~~~~~~~~~~~~ | 28359 Bremen/Germany
~~~~~~~~~~~~~~~~~~~~
From | Date | Subject | |
---|---|---|---|
Next Message | Ulf Mehlig | 1998-10-26 08:58:52 | Re: [GENERAL] date null |
Previous Message | ulrich | 1998-10-26 07:33:43 | tm |