Thread
-
select and join
ZioBudda <michel@michel.enter.it> — 1998-10-23T12:22:14Z
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@enter.it - http://ziobudda.enter.it/ Linux Problem? Ask to linux@media.dsi.unimi.it "/dev/ziobudda: access to /var/tmp/beer denied, use /var/adm/pineapple" -
RE: [GENERAL] select and join
Taral <taral@mail.utexas.edu> — 1998-10-23T17:56:16Z
> 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 I keep seeing this... When doing something like this, you must have ALL non-aggregate columns in the GROUP BY. So: (note you also forgot to update the FROM) SELECT utente.cognome, prestito.id_utente, libro.tipo, count(*) FROM prestito, libro, utente WHERE libro.id_libro = prestito.id_libro AND utente.id_utente = prestito.id_utente GROUP BY cognome, id_utente, tipo; Taral
-
Re: [GENERAL] select and join
Ulf Mehlig <umehlig@uni-bremen.de> — 1998-10-26T08:47:02Z
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 ~~~~~~~~~~~~~~~~~~~~