On Sat, Sep 29, 2012 at 9:02 AM, Andreas <maps...@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
