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
