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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2012-09-30 16:26:00 | Re: Reuse temporary calculation results in an SQL update query [SOLVDED] |
Previous Message | Victor Sterpu | 2012-09-29 18:28:45 | Re: Need help with a special JOIN |