From: Victor Sterpu <victor@caido.ro>
To: pgsql-sql@postgresql.org
Subject: Re: Need help with a special JOIN
Date: Sat, 29 Sep 2012 21:28:45 +0300
Message-ID: <50673DDD.5070605@caido.ro> (raw)
In-Reply-To: <50671B8F.4090504@gmx.net>
References: <50671B8F.4090504@gmx.net>
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?
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: victor@caido.ro
Subject: Re: Need help with a special JOIN
In-Reply-To: <50673DDD.5070605@caido.ro>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox