Re: Need help with a special JOIN

On Sat, Sep 29, 2012 at 9:02 AM, 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?
>
>
I'm not sure it is any more elegant than the kind of solution you suggest,
but this works:

# select id, name, value from
(select *, count(o.id) over (partition by o.id) as total from objects o
join attributes a on a.object_id = o.id or a.object_id = 0) q
where total = 1 or object_id != 0;
id | name | value
----+------+-------
1 | A | 42
2 | B | 99
3 | C | 42