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?
>
>
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 |