Thread: problems with left outer join
Hi,
I would like to make a left outer join between two tables (A and B) and then only select rows of table A which do not apply on the join condition (where B is null)
simple example:
create table A(
id int primary key
);
create table B(
id int primary key
);
insert into A values(1);
insert into A values(2);
insert into B values(1);
The goal is now to make a join between A and B and to select only the row from A with the id = 2 (because there is no corresponding id in Table B)
So I tried:
select *
from A t1 left outer join B t2 on t1.id = t2.id
where t2.id = null
but I dont get any row returned. I expect the result: 2 | NULL
if I execute
select *
from A t1 left outer join B t2 on t1.id = t2.id
I get:
1) 2 | NULL
2) 1 | 1
what am I doing wrong ?
thanx in advance,
Andreas
I would like to make a left outer join between two tables (A and B) and then only select rows of table A which do not apply on the join condition (where B is null)
simple example:
create table A(
id int primary key
);
create table B(
id int primary key
);
insert into A values(1);
insert into A values(2);
insert into B values(1);
The goal is now to make a join between A and B and to select only the row from A with the id = 2 (because there is no corresponding id in Table B)
So I tried:
select *
from A t1 left outer join B t2 on t1.id = t2.id
where t2.id = null
but I dont get any row returned. I expect the result: 2 | NULL
if I execute
select *
from A t1 left outer join B t2 on t1.id = t2.id
I get:
1) 2 | NULL
2) 1 | 1
what am I doing wrong ?
thanx in advance,
Andreas
On 10:13 Thu 05 Oct , Andreas Andreakis wrote: > Hi, > > So I tried: > > select * > from A t1 left outer join B t2 on t1.id = t2.id > where t2.id = null Swap '=' with 'is' Regards, Andy ___________________________________________________________ All new Yahoo! Mail "The new Interface is stunning in its simplicity and ease of use." - PC Magazine http://uk.docs.yahoo.com/nowyoucan.html
"Andreas Andreakis" <andreas.andreakis@googlemail.com> writes: > So I tried: > select * > from A t1 left outer join B t2 on t1.id = t2.id > where t2.id = null You need "IS NULL", not "= NULL". See http://www.postgresql.org/docs/8.1/static/functions-comparison.html regards, tom lane