Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <maps.on@gmx.net>) id 1THzUr-00034F-1I
	for pgsql-sql@postgresql.org; Sat, 29 Sep 2012 16:02:29 +0000
Received: from mailout-de.gmx.net ([213.165.64.23])
	by magus.postgresql.org with smtp (Exim 4.72)
	(envelope-from <maps.on@gmx.net>) id 1THzUm-00084i-FT
	for pgsql-sql@postgresql.org; Sat, 29 Sep 2012 16:02:28 +0000
Received: (qmail invoked by alias); 29 Sep 2012 16:02:22 -0000
Received: from mue-88-130-51-121.dsl.tropolys.de (EHLO [192.168.1.113])
	[88.130.51.121]
	by mail.gmx.net (mp071) with SMTP; 29 Sep 2012 18:02:22 +0200
X-Authenticated: #14269776
X-Provags-ID: V01U2FsdGVkX198VAJCRet8rAuTJzu11MDjfruhghPBA84jMWTRP6
	KvAj1GOQP51bCm
Message-ID: <50671B8F.4090504@gmx.net>
Date: Sat, 29 Sep 2012 18:02:23 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
	rv:15.0) Gecko/20120907 Thunderbird/15.0.1
MIME-Version: 1.0
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Subject: Need help with a special JOIN
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Y-GMX-Trusted: 0
X-Pg-Spam-Score: -2.7 (--)
X-Archive-Number: 201209/64
X-Sequence-Number: 36866

Hi,

asume I've got 2 tables

objects ( id int, name text )
attributes ( object_id int, value int )

attributes   has a default entry with object_id = 0 and some other where 
another value should be used.

e.g.
objects
(   1,   'A'   ),
(   2,   'B'   ),
(   3,   'C'   )

attributes
(   0,   42   ),
(   2,   99   )

The result of the join should look like this:

object_id, name, value
1,   'A',   42
2,   'B',   99
3,   'C',   42


I could figure something out with 2 JOINs, UNION and some DISTINCT ON 
but this would make my real query rather chunky.   :(

Is there an elegant way to get this?