Thread: Need help with a special JOIN

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?

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
presentotherwise taking the default. 

David J.

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?
>
>