subselect and left join not working?

From: Jorge Arenas <jorge(dot)arenas(at)kamarble(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: subselect and left join not working?
Date: 2010-11-29 05:38:48
Message-ID: 749730.36457.qm@web113802.mail.gq1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi,

I am using postgres 8.4 and I am having problems doing a very simple query:

select zona_id from zonas where zona_id not in (select zona_id from usuarios
where per_id =2)

but I get no records in return.

The problem is that, if I I break down the quey, I get:

select zona_id from zonas

"A"
"B"
"C"
"D"
"E"
"F"
"FCHIH"
"FCOAH"
"DGO"
"T"
"FGTO"
"FGRO"
"FQRO"
"FHGO"
"JAL"
"MOR"
"FPUE"
"FQROO"
"FSLP"
"F VER"
"FYUC"
""

select zona_id from usuarios where per_id =2

"E"
"F"
"FCHIH"
"JAL"
"FPUE"
"F VER"
"C"
"D"
"A"
""

but with

select zona_id from zonas where zona_id not in (select zona_id from usuarios
where per_id =2)

I dont' get records when I am expecting to see this:

"B"
"FCOAH"
"DGO"
"T"
"FGTO"
"FGRO"
"FQRO"
"FHGO"
"MOR"
"FQROO"
"FSLP"
"FYUC"
""

What's wrong? Please help. Any clues?

---
It also does not work a left join on 'zonas'. Some 'zonas' are designated to
some users but in my case is not wroking:

t1
usr zone
1 A
2 C
3 D

t2
zone
A
B
C
D
E

I want to see
zone usr
A 1
B
C 2
D 3
E

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Jasen Betts 2010-11-29 09:44:44 Re: subselect and left join not working?
Previous Message Stefan Becker 2010-11-28 23:30:47 Is there a solution for "SELECT OR INSERT"