Skip site navigation (1) Skip section navigation (2)

Re: Need help with a special JOIN

From: Johnny Winn <j(dot)winn(dot)v(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Need help with a special JOIN
Date: 2012-09-29 19:11:33
Message-ID: CALBtUW6kRhvJ0MeCu3CJe+UsKVySvn=Kri--M18CRS5iwaYupw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

In response to

pgsql-sql by date

Next:From: David JohnstonDate: 2012-09-30 16:26:00
Subject: Re: Reuse temporary calculation results in an SQL update query [SOLVDED]
Previous:From: Victor SterpuDate: 2012-09-29 18:28:45
Subject: Re: Need help with a special JOIN

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group