DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To;
	b=NdedJwHcpWmo7RMyFlPpFkSaRICVTM9aNU81E1XSmS69Y5yWuCSCEh9RCnBggoaVySyJpunpxvgaXOXLjVgHEDl9OQmrcpbZDmutO9c1y9bbGkk36wUGWc/YxkIZiAmItQiozOelBza44ehG2vTUDy8Z5F1UHwjCYu4CUyrSvEk=
	;
References: <50671B8F.4090504@gmx.net>
In-Reply-To: <50671B8F.4090504@gmx.net>
Mime-Version: 1.0 (1.0)
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii
Message-Id: <DB6EC00D-D070-4D7A-9208-2A872D040EB0@yahoo.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: Need help with a special JOIN
Date: Sat, 29 Sep 2012 13:33:56 -0400
To: Andreas <maps.on@gmx.net>

On Sep 29, 2012, at 12:02, Andreas <maps.on@gmx.net> wrote:

> Hi,
>=20
> asume I've got 2 tables
>=20
> objects ( id int, name text )
> attributes ( object_id int, value int )
>=20
> attributes   has a default entry with object_id =3D 0 and some other where=
 another value should be used.
>=20
> e.g.
> objects
> (   1,   'A'   ),
> (   2,   'B'   ),
> (   3,   'C'   )
>=20
> attributes
> (   0,   42   ),
> (   2,   99   )
>=20
> The result of the join should look like this:
>=20
> object_id, name, value
> 1,   'A',   42
> 2,   'B',   99
> 3,   'C',   42
>=20
>=20
> I could figure something out with 2 JOINs, UNION and some DISTINCT ON but t=
his would make my real query rather chunky.   :(
>=20
> Is there an elegant way to get this?
>=20

General form (idea only, syntax not tested)

Select objectid, name, coalesce(actuals.value, defaults.value)
=46rom objects cross join (select ... =46rom  attributes ...) as defaults
Left join attributes as actuals on ...

Build up a master relation with all defaults then left join that against the=
 attributes taking the matches where present otherwise taking the default.

David J.