Hello,
I want to list the rows of a table with a text field whose values do not
exist in a similar field of another table. Basically what I want to get
is negated results of a join.
Lets say the tables table_a and table_b have the field name.
table_a table_b
name age name
----- --- -----
Peter 27 Paul
Paul 42
Mary 20
If I asked for a join like this:
SELECT table_a.name,table_a.age FROM table_a,table_b WHERE table_a.name=table_b.name
I would get:
name age
----- ---
Paul 42
But I want the opposite. I tried a non-equi join like this:
SELECT table_a.name,table_a.age FROM table_a,table_b WHERE table_a.name<>table_b.name
and I got:
name age
----- ---
Peter 27
Mary 20
It worked except for the case when table_b is empty. In this case the
nothing was returned. Is this the expected behaviour or is it a bug in
PostgreSQL?
How can I make a query that works the way I want all the time, even for the
case when table_b is empty?
Regards,
Manuel Lemos
E-mail: mlemos@acm.org
URL: http://www.e-na.net/the_author.html
PGP key: finger://mlemos@zeus.ci.ua.pt
--
