[SQL] subselect and left join not working?

[Jorge Arenas]

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
usrzone
1   A
2   C
3   D

t2
zone
A
B
C
D
E

I want to see
zoneusr
A1
B
C2
D3
E

[SQL] Is there a solution for "SELECT OR INSERT"

[Stefan Becker]

given a simply table table like this:

create table MyTabl 
(id serial,
 lname varchar,
 primary key (id)
)

I am trying to return the Primary-Key for a given
row regardless of the wanted row already being 
in the table.

using something like:

select coalesce 
(
  (select id from MyTabl where lname='wanted'),
  (insert into MyTabl (lname) values ('wanted') returning id)
) as id;

the above doesn't allow the insert..returning even thow
a similar select statement would be okay.
Is there a way to get the ID row OR create a new one in
ONE single statement?

my best regards and thank-you 
for your time

Stefan




-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql