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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-sql by date

  From Date Subject
Next Message Johnny Winn 2012-09-29 19:11:33 Re: Need help with a special JOIN
Previous Message David Johnston 2012-09-29 17:33:56 Re: Need help with a special JOIN