Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 7EDC91337BC5 for ; Mon, 4 Oct 2010 10:24:51 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 96545-08 for ; Mon, 4 Oct 2010 13:24:46 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pw0-f46.google.com (mail-pw0-f46.google.com [209.85.160.46]) by mail.postgresql.org (Postfix) with ESMTP id 146301337BC3 for ; Mon, 4 Oct 2010 10:24:43 -0300 (ADT) Received: by pwj5 with SMTP id 5so1112195pwj.19 for ; Mon, 04 Oct 2010 06:24:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:cc :subject:references:mime-version:content-type:content-disposition :in-reply-to:user-agent; bh=XcaCMFHzq2zJSJn5yiP88RtIK43k7kpaq+VRvJg0vLE=; b=SJY8oqwYwqQXfYjNHIvI7AQyb7WK01XTsTruWpNgrAp6vk93GzFyFtv5x/iYMlOCds jZ7LmOW2/c79FYguR8FOTWzTSYXJ+m7jTRt5TO252luwnIoW10gLOTuTz4LLmFgDvRCJ HqnrJaHiWXKkogHeGubdoIRDO7oitrwjyxxaw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:cc:subject:references:mime-version :content-type:content-disposition:in-reply-to:user-agent; b=ZlYwEfxdInMwUDlJyZbfu+c4OgZmREkka3cJCmObY7aE9m5WU5FUK7dv6k55PeNiO0 5WUUx4Mdazp9HdRVPaoHTYZCwzlFyKIboJx1QVDslHb/QMPhyT/J3+wz6B1E4awqWev4 vCuviQb8ep6+j//QcNUjuFFJMYGRoL9U3jTm4= Received: by 10.142.221.19 with SMTP id t19mr8646553wfg.235.1286198683846; Mon, 04 Oct 2010 06:24:43 -0700 (PDT) Received: from eddie (c-67-186-249-122.hsd1.ut.comcast.net [67.186.249.122]) by mx.google.com with ESMTPS id t38sm6454833wfc.9.2010.10.04.06.24.41 (version=TLSv1/SSLv3 cipher=RC4-MD5); Mon, 04 Oct 2010 06:24:42 -0700 (PDT) Message-ID: <4ca9d59a.26798e0a.1059.25cf@mx.google.com> Date: Mon, 4 Oct 2010 07:24:39 -0600 From: Joshua Tolley To: Frank Bax Cc: pgsql-sql@postgresql.org Subject: Re: join returns too many results... References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="oFbHfjnMgUMsrGjO" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.18 (2008-05-17) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201010/13 X-Sequence-Number: 34664 --oFbHfjnMgUMsrGjO Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Sun, Oct 03, 2010 at 02:54:41PM -0400, Frank Bax wrote: > Whatever clause I choose to add I *must* ensure that final result set =20 > contains only one-to-one join between tables. Either of these two =20 > results is acceptable: > > For option 1; result C=3DAll or C=3DCentre is acceptable. > For option 2; result B=3DNE70 or B=3DNE75 or B=3DNE80 is acceptable. > > I am lost trying to construct SQL to accomplish this... Try DISTINCT ON, after ensuring the results are ordered meaningfully. http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-DIST= INCT -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com --oFbHfjnMgUMsrGjO Content-Type: application/pgp-signature; name="signature.asc" Content-Description: Digital signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkyp1ZcACgkQRiRfCGf1UMPW9wCeJUmoJ3cGWE084Dc9x5qutSfe HDUAn3vgKA5lhTa09Ax7/TN3n7PV8Sjk =iGK6 -----END PGP SIGNATURE----- --oFbHfjnMgUMsrGjO--