Message-ID: <50673DDD.5070605@caido.ro>
Date: Sat, 29 Sep 2012 21:28:45 +0300
From: Victor Sterpu <victor@caido.ro>
User-Agent: Mozilla/5.0 (Windows NT 6.1;
	rv:15.0) Gecko/20120907 Thunderbird/15.0.1
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Need help with a special JOIN
References: <50671B8F.4090504@gmx.net>
In-Reply-To: <50671B8F.4090504@gmx.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

This is a way to do it, but things will change if you have many
attributes/object

SELECT o.*, COALESCE(a1.value, a2.value)
FROM objects AS o
LEFT JOIN attributes AS a1 ON (a1.object_id = o.id)
LEFT JOIN attributes AS a2 ON (a2.object_id = 0);

On 29.09.2012 19:02, Andreas wrote:
> 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?
>
>