DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=bkgkJ0wo259Bbn9WtbS0eCFkvpEPNYpbU8MvQgCBF3qAh9XhbvjO05WTg1DlUFUGPq
	zpNGopSJ+ICWuNAoT81+88J34jjTM4Ec0A84saDPuo5R9hJ4Ep9iTzrUhvzKTJ2XO7e0
	5S4XRhu878+lOCDX5DsH51/OtHUYl6Y+vgrmY=
MIME-Version: 1.0
In-Reply-To: <87y629rl34.fsf@kolob.subpolar.dyndns.org>
References: <87y629rl34.fsf@kolob.subpolar.dyndns.org>
Date: Sun, 15 May 2011 07:39:06 +0900
Message-ID: <BANLkTinOMHW_j0e6-6BHr4BOGLZKwEEZDA@mail.gmail.com>
Subject: Re: self join
From: Ian Lawrence Barwick <barwick@gmail.com>
To: Seb <spluque@gmail.com>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi

2011/5/15 Seb <spluque@gmail.com>:
> Hi,
>
> This probably reflects my confusion with how self joins work.
>
> Suppose we have this table:
>
> =3D# SELECT * FROM tmp;
> =C2=A0a | b
> ---+---
> =C2=A01 | 2
> =C2=A02 | 3
> =C2=A04 | 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:
>
> =3D# SELECT * FROM tmp t1, tmp t2 WHERE t2.b <> t1.a;
> =C2=A0a | b | a | b
> ---+---+---+---
> =C2=A01 | 2 | 1 | 2
> =C2=A01 | 2 | 2 | 3
> =C2=A01 | 2 | 4 | 5
> =C2=A02 | 3 | 2 | 3
> =C2=A02 | 3 | 4 | 5
> =C2=A04 | 5 | 1 | 2
> =C2=A04 | 5 | 2 | 3
> =C2=A04 | 5 | 4 | 5
> (8 rows)
>
> I need to get:
>
> =C2=A0a | b | a | b
> ---+---+---+---
> =C2=A01 | 2 | 1 | 2
> =C2=A04 | 5 | 4 | 5
>
> Or just:
>
> =C2=A0a | b
> ---+---
> =C2=A01 | 2
> =C2=A04 | 5

Your query doesn't have an explicit join and is producing a cartesian resul=
t.

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=3Dt1.a);


HTH


Ian Lawrence Barwick