On Sat, Sep 29, 2012 at 9:02 AM, Andreas
<maps.on@gmx.net> 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?
I'm not sure it is any more elegant than the kind of solution you suggest, but this works:
# select id, name, value from
(select *, count(
o.id) over (partition by
o.id) as total from objects o join attributes a on a.object_id =
o.id or a.object_id = 0) q
where total = 1 or object_id != 0;
id | name | value
----+------+-------
1 | A | 42
2 | B | 99
3 | C | 42