Re: Need help with a special JOIN - Mailing list pgsql-sql

From Johnny Winn
Subject Re: Need help with a special JOIN
Date
Msg-id CALBtUW6kRhvJ0MeCu3CJe+UsKVySvn=Kri--M18CRS5iwaYupw@mail.gmail.com
Whole thread Raw
In response to Re: Need help with a special JOIN  (Victor Sterpu <victor@caido.ro>)
List pgsql-sql
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


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


pgsql-sql by date:

Previous
From: Victor Sterpu
Date:
Subject: Re: Need help with a special JOIN
Next
From: "David Johnston"
Date:
Subject: Re: Reuse temporary calculation results in an SQL update query [SOLVDED]