Received: from neptuno.uevora.pt (neptuno.sc.uevora.pt [193.136.216.24]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA65908 for ; Fri, 3 Dec 1999 11:11:32 -0500 (EST) (envelope-from mjnf@neptuno.sc.uevora.pt) Received: from neptuno.uevora.pt (really [127.0.0.1]) by neptuno.uevora.pt via smail with esmtp (ident mjnf using rfc1413) id (Debian Smail3.2.0.102) for ; Fri, 3 Dec 1999 16:46:37 +0000 (WET) Sender: mjnf Message-ID: <3847F3ED.98D71567@neptuno.uevora.pt> Date: Fri, 03 Dec 1999 16:46:37 +0000 From: Mario Jorge Nunes Filipe Reply-To: mjnf@uevora.pt Organization: Universidade de Evora X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.13 i686) X-Accept-Language: pt,en MIME-Version: 1.0 To: "pgsql-sql@postgreSQL.org" Subject: Re: [SQL] Wierd stuff Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Mario Jorge Nunes Filipe wrote: > > Hi > > I'm developing a system with a large number of tables. > > One of the tables has (among others) the following fields: > Table = aluno > +----------------------------------+--------------------------------- > | Field | Type > +----------------------------------+--------------------------------- > | numero_aluno | int4 > | freguesia_naturalidade | int2 > | concelho_naturalidade | int2 > | nacionalidade | int2 > > another is like this: > > -- Table = concelho > +----------------------------------+--------------------------------- > | Field | Type > +----------------------------------+--------------------------------- > | codigo | int4 > | nome | text > > The field "concelho_naturalidade" on the first table has values taken > from the "codigo" field from the second. > > Now comes the problem: > > When i do : > sac=> select a.numero_aluno, a.concelho_naturalidade from aluno a where > a.numero_aluno=13792; > > i get: > > numero_aluno|concelho_naturalidade > ------------+--------------------- > 13792| 1006 > (1 row) > > and whith: > sac=> select * from concelho where codigo=1006; > > i get: > > codigo|nome > ------+---------------- > 1006|CALDAS DA RAINHA > (1 row) > > but with: > sac=> select c.nome from aluno a, concelho c where > a.concelho_naturalidade=c.codigo and a.numero_aluno=13792; > > i get: > > nome > ---- > (0 rows) > > If my SQL is worth anything I thought it would reply "Caldas da Rainha". > What is wrong here? And now... even wierder (?): sac=> select a.numero_aluno, a.concelho_naturalidade from aluno a sac-> where sac-> a.numero_aluno=15897; numero_aluno|concelho_naturalidade ------------+--------------------- 15897| 0 (1 row) sac=> select * from concelho where codigo=0; codigo|nome ------+----------- 0|A Preencher (1 row) sac=> select c.nome from aluno a, concelho c where sac-> a.concelho_naturalidade=c.codigo and a.numero_aluno=15897; nome ----------- A Preencher (1 row) this is the expected output... the first one isn't. So does anyone have a clue on what's going on? This is not particularly critical has i can change the third query fro the first two, only it becomes very inneficient. Thanks -- Mario Filipe mjnf@uevora.pt http://neptuno.sc.uevora.pt/~mjnf