From: Ian Lawrence Barwick <barwick@gmail.com>
To: Seb <spluque@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: self join
Date: Sun, 15 May 2011 07:39:06 +0900
Message-ID: <BANLkTinOMHW_j0e6-6BHr4BOGLZKwEEZDA@mail.gmail.com> (raw)
In-Reply-To: <87y629rl34.fsf@kolob.subpolar.dyndns.org>
References: <87y629rl34.fsf@kolob.subpolar.dyndns.org>
Hi
2011/5/15 Seb <spluque@gmail.com>:
> 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
Your query doesn't have an explicit join and is producing a cartesian result.
I don't think a self- join will work here; a subquery should produce the
result you're after:
SELECT *
FROM tmp t1
WHERE NOT EXISTS(SELECT TRUE FROM tmp t2 WHERE t2.b=t1.a);
HTH
Ian Lawrence Barwick
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: barwick@gmail.com, spluque@gmail.com
Subject: Re: self join
In-Reply-To: <BANLkTinOMHW_j0e6-6BHr4BOGLZKwEEZDA@mail.gmail.com>
* 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