Hi,
I've following select, and I expect to receive a single record as result from it:
select c.id from copie as c where c.enum=46857 and c.condizio_prestito = 'A' and c.id not in (select id_copia from testi_fermi_prenotati) and c.id not in (select id_copia from prestiti);
but it doesn't give me any records at all.
Following there is table descriptions:
CREATE TABLE copie ( id serial NOT NULL primary key, enum integer, id_biblioteca integer NOT NULL, serie text, collocazione text, note text, condizio_prestito character(1) DEFAULT 'A'::bpchar, ctime date DEFAULT now(), mtime date, inventario integer );
CREATE TABLE prestiti ( id serial NOT NULL, inizio date DEFAULT now(), id_libro text, id_utente text, fine date, scadenza date NOT NULL, stato smallint DEFAULT 1, id_copia integer references copie(id) );
CREATE TABLE testi_fermi_prenotati ( id_copia integer NOT NULL references copie(id), id_prenotazione integer NOT NULL, fermato_il timestamp without time zone DEFAULT now() NOT NULL, scadenza timestamp without time zone, stato character(1) DEFAULT 'a'::bpchar );
and some usefull query results:
prove=> select * from copie where enum=46857;
id | enum | id_biblioteca | serie | collocazione | note | condizio_prestito | ctime | mtime | inventario
-------+-------+---------------+-------+--------------+------+-------------------+------------+-------+------------
37163 | 46857 | 1 | | F RRN MAY | | A | 2004-05-03 | | 41576
(1 row)
(SEE THE FOLLOWING QUERY AND RESULT) prove=> select * from prestiti where id_copia=37163; id | inizio | id_libro | id_utente | fine | scadenza | stato | id_copia ----+--------+----------+-----------+------+----------+-------+---------- (0 rows)
prove=> select * from testi_fermi_prenotati where id_copia=37163; id_copia | id_prenotazione | fermato_il | scadenza | stato ----------+-----------------+------------+----------+------- (0 rows)
prove=> select c.id from copie as c where c.enum=46857 and c.condizio_prestito = 'A' and c.id not in (select id_copia from testi_fermi_prenotati) and c.id not in (select id_copia from prestiti);
id ---- (0 rows)
prove=> select c.id from copie as c where c.enum=46857 and c.condizio_prestito = 'A' and c.id not in (select id_copia from testi_fermi_prenotati); id ------- 37163 (1 row)
prove=> select c.id from copie as c where c.enum=46857 and c.condizio_prestito = 'A' and c.id not in (select id_copia from prestiti); id ---- (0 rows)
prove=> explain analyze select c.id from copie as c where c.enum=46857 and c.condizio_prestito = 'A' and c.id not in (select id_copia from testi_fermi_prenotati) and c.id not in (select id_copia from prestiti);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Seq Scan on copie c (cost=0.00..14587.17 rows=1 width=4) (actual time=15.82..15.82 rows=0 loops=1)
Filter: ((enum = 46857) AND (condizio_prestito = 'A'::bpchar) AND (subplan) AND (subplan))
SubPlan
-> Seq Scan on testi_fermi_prenotati (cost=0.00..0.00 rows=1 width=4) (actual time=0.00..0.00 rows=0 loops=1)
-> Seq Scan on prestiti (cost=0.00..23.41 rows=1241 width=4) (actual time=0.01..10.21 rows=1241 loops=1)
Total runtime: 15.95 msec
(6 rows)
prove=> explain select c.id
from copie as c
where c.enum=46857 and
c.condizio_prestito = 'A' and
c.id not in (select id_copia from testi_fermi_prenotati) and
c.id not in (select id_copia from prestiti);
QUERY PLAN
----------------------------------------------------------------------------------------------
Seq Scan on copie c (cost=0.00..14587.17 rows=1 width=4)
Filter: ((enum = 46857) AND (condizio_prestito = 'A'::bpchar) AND (subplan) AND (subplan))
SubPlan
-> Seq Scan on testi_fermi_prenotati (cost=0.00..0.00 rows=1 width=4)
-> Seq Scan on prestiti (cost=0.00..23.41 rows=1241 width=4)
(5 rows)
If you want to play with these data: http://diff.homeunix.net/anomalia.sql.gz
then
gzip -d anomalias.sql createdb anydb psql -f anomalia.sql anydb
Best wishes, \ferz
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly