Horton

Thread

  1. Need help with a special JOIN

    Andreas <maps.on@gmx.net> — 2012-09-29T16:02:23Z

    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?

  2. Re: Need help with a special JOIN

    Samuel Gendler <sgendler@ideasculptor.com> — 2012-09-29T17:32:09Z

    On Sat, Sep 29, 2012 at 9:02 AM, 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?
>
>
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

  3. Re: Need help with a special JOIN

    David Johnston <polobo@yahoo.com> — 2012-09-29T17:33:56Z

    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.

  4. Re: Need help with a special JOIN

    Victor Sterpu <victor@caido.ro> — 2012-09-29T18:28:45Z

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

  5. Re: Need help with a special JOIN

    Johnny Winn <j.winn.v@gmail.com> — 2012-09-29T19:11:33Z

    On Sat, Sep 29, 2012 at 2:28 PM, Victor Sterpu <victor@caido.ro> wrote:

> 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?
>>
>>
>>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql<http://www.postgresql.org/mailpref/pgsql-sql>
>


I like this join option because it's a little more simplified. Depending on
the "default option" requirement you could change the nested select or
otherwise replace all together.

 SELECT "Objects"."ID", "Objects"."Name",
       COALESCE("Attributes".value, (SELECT "Attributes".value FROM
"Attributes" WHERE object_id = 0))
 FROM "Objects" LEFT JOIN
       "Attributes" ON "Objects"."ID" = "Attributes".object_id;

Thanks,
Johnny