On Sat, Sep 29, 2012 at 2:28 PM, Victor Sterpu <vic...@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<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
