Re: Need help with a special JOIN

On Sat, Sep 29, 2012 at 2:28 PM, Victor Sterpu <victor(at)caido(dot)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(at)postgresql(dot)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