From: Seb <spluque@gmail.com>
To: pgsql-sql@postgresql.org
Subject: self join
Date: Sat, 14 May 2011 17:09:03 -0500
Message-ID: <87y629rl34.fsf@kolob.subpolar.dyndns.org> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spluque@gmail.com
Subject: Re: self join
In-Reply-To: <87y629rl34.fsf@kolob.subpolar.dyndns.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox