From: | ZioBudda <michel(at)michel(dot)enter(dot)it> |
---|---|
To: | pgsql-general(at)postgreSQL(dot)org |
Subject: | select and join |
Date: | 1998-10-23 12:22:14 |
Message-ID: | Pine.LNX.4.03.9810231421550.3855-100000@michel.enter.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi, I have this 3 table:
esame=> \d prestito
Table = prestito
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| id_libro | varchar() not null | 10 |
| id_utente | int4 not null | 4 |
| data_prestito | date | 4 |
| data_restituzione | date | 4 |
| n_gg_prestito | int4 not null | 4 |
| notifica1 | date | 4 |
| notifica2 | date | 4 |
| notifica3 | date | 4 |
+----------------------------------+----------------------------------+-------+
Table = libro
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| titolo | varchar() not null | 80 |
| id_libro | varchar() not null | 10 |
| id_utente | int4 | 4 |
| collocazione | varchar() not null | 10 |
| casa_edit | varchar() | 20 |
| data_publ | date | 4 |
| tipo | int4 | 4 |
| difetto | varchar() | 40 |
+----------------------------------+----------------------------------+-------+
Table = utente
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| cognome | varchar() not null | 20 |
| nome | varchar() | 20 |
| id_utente | int4 not null default nextval ( | 4 |
| indirizzo | varchar() | 80 |
| telefono | varchar() not null | 14 |
| tipo_utente | int4 | 4 |
| info_utente | varchar() not null | 40 |
| libri_presi | int4 | 4 |
| notifica1 | date | 4 |
| notifica2 | date | 4 |
| notifica3 | date | 4 |
| password | varchar() | 10 |
+----------------------------------+----------------------------------+-------+
Now, when I make this select:
esame=> select prestito.id_utente, libro.tipo, count(*) from prestito, libro where libro.id_libro = prestito.id_libro group by id_utente, tipo\g
the output is (it is correct, too):
id_utente|tipo|count
---------+----+-----
9| 1| 1
19| 1| 2
19| 2| 1
(3 rows)
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
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
but the output is :
ERROR: parser: illegal use of aggregates or non-group column in target list
Or is beeter get the first select (the select without the "utente.cognome") and make a
select foreach "id_utente" so that I get "utente.cognome".
thanks in advance.
PS: I have buy this book: "The pratical sql handbook (3th edition - 1996)". How is it ?
good ? bleah?
"Il divertimento e' giusto se la scimmia ci prende gusto"
--
Pluto Linux Press: http://ziobudda.enter.it/PLP
--
Morelli 'ZioBudda' Davide Michel - Member of Pluto Linux User Group
michel(at)enter(dot)it - http://ziobudda.enter.it/
Linux Problem? Ask to linux(at)media(dot)dsi(dot)unimi(dot)it
"/dev/ziobudda: access to /var/tmp/beer denied, use /var/adm/pineapple"
From | Date | Subject | |
---|---|---|---|
Next Message | ZioBudda | 1998-10-23 12:47:04 | date null |
Previous Message | ulrich | 1998-10-23 08:03:05 | repl |