Re: Need help with a special JOIN

On Sep 29, 2012, at 12:02, Andreas <maps(dot)on(at)gmx(dot)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.