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

Re: Need help with a special JOIN

From: Victor Sterpu <victor(at)caido(dot)ro>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Need help with a special JOIN
Date: 2012-09-29 18:28:45
Message-ID: 50673DDD.5070605@caido.ro (view raw or flat)
Thread:
Lists: pgsql-sql
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?
>
>



In response to

Responses

pgsql-sql by date

Next:From: Johnny WinnDate: 2012-09-29 19:11:33
Subject: Re: Need help with a special JOIN
Previous:From: David JohnstonDate: 2012-09-29 17:33:56
Subject: Re: Need help with a special JOIN

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