To: pgsql-sql@postgresql.org
From: Seb <spluque@gmail.com>
Subject: self join
Date: Sat, 14 May 2011 17:09:03 -0500
Organization: Church of Emacs
Lines: 47
Message-ID: <87y629rl34.fsf@kolob.subpolar.dyndns.org>
Mime-Version: 1.0
Content-Type: text/plain
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.3 (gnu/linux)
Cancel-Lock: sha1:SOQhJeuS7Lv4jWSuIRa96Cud2m0=

Hi,

This probably reflects my confusion with how self joins work.

Suppose we have this table:

=# SELECT * FROM tmp;
 a | b 
---+---
 1 | 2
 2 | 3
 4 | 5
(3 rows)

If I want to get a table with records where none of the values in column
b are found in column a, I thought this should do it:

=# SELECT * FROM tmp t1, tmp t2 WHERE t2.b <> t1.a;
 a | b | a | b 
---+---+---+---
 1 | 2 | 1 | 2
 1 | 2 | 2 | 3
 1 | 2 | 4 | 5
 2 | 3 | 2 | 3
 2 | 3 | 4 | 5
 4 | 5 | 1 | 2
 4 | 5 | 2 | 3
 4 | 5 | 4 | 5
(8 rows)

I need to get:

 a | b | a | b 
---+---+---+---
 1 | 2 | 1 | 2
 4 | 5 | 4 | 5

Or just:

 a | b  
---+---
 1 | 2 
 4 | 5 


-- 
Seb