From: David Johnston <polobo@yahoo.com>
To: Andreas <maps.on@gmx.net>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Need help with a special JOIN
Date: Sat, 29 Sep 2012 13:33:56 -0400
Message-ID: <DB6EC00D-D070-4D7A-9208-2A872D040EB0@yahoo.com> (raw)
In-Reply-To: <50671B8F.4090504@gmx.net>
References: <50671B8F.4090504@gmx.net>
On Sep 29, 2012, at 12:02, Andreas <maps.on@gmx.net> 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?
>
General form (idea only, syntax not tested)
Select objectid, name, coalesce(actuals.value, defaults.value)
From objects cross join (select ... From 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.
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: polobo@yahoo.com, maps.on@gmx.net
Subject: Re: Need help with a special JOIN
In-Reply-To: <DB6EC00D-D070-4D7A-9208-2A872D040EB0@yahoo.com>
* 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