pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed help with a special JOIN
5+ messages / 5 participants
[nested] [flat]
* Need help with a special JOIN
@ 2012-09-29 16:02 Andreas <maps.on@gmx.net>
0 siblings, 3 replies; 5+ messages in thread
From: Andreas @ 2012-09-29 16:02 UTC (permalink / raw)
To: pgsql-sql
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help with a special JOIN
@ 2012-09-29 17:32 Samuel Gendler <sgendler@ideasculptor.com>
parent: Andreas <maps.on@gmx.net>
2 siblings, 0 replies; 5+ messages in thread
From: Samuel Gendler @ 2012-09-29 17:32 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help with a special JOIN
@ 2012-09-29 17:33 David Johnston <polobo@yahoo.com>
parent: Andreas <maps.on@gmx.net>
2 siblings, 0 replies; 5+ messages in thread
From: David Johnston @ 2012-09-29 17:33 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
On Sep 29, 2012, at 12:02, 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?
>
General form (idea only, syntax not tested)
Select objectid, name, coalesce(actuals.value, defaults.value)
From objects cross join (select ... From attributes ...) as defaults
Left join attributes as actuals on ...
Build up a master relation with all defaults then left join that against the attributes taking the matches where present otherwise taking the default.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help with a special JOIN
@ 2012-09-29 18:28 Victor Sterpu <victor@caido.ro>
parent: Andreas <maps.on@gmx.net>
2 siblings, 1 reply; 5+ messages in thread
From: Victor Sterpu @ 2012-09-29 18:28 UTC (permalink / raw)
To: 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?
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Need help with a special JOIN
@ 2012-09-29 19:11 Johnny Winn <j.winn.v@gmail.com>
parent: Victor Sterpu <victor@caido.ro>
0 siblings, 0 replies; 5+ messages in thread
From: Johnny Winn @ 2012-09-29 19:11 UTC (permalink / raw)
To: pgsql-sql
On Sat, Sep 29, 2012 at 2:28 PM, Victor Sterpu <victor@caido.ro> wrote:
> 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?
>>
>>
>>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql<http://www.postgresql.org/mailpref/pgsql-sql;
>
I like this join option because it's a little more simplified. Depending on
the "default option" requirement you could change the nested select or
otherwise replace all together.
SELECT "Objects"."ID", "Objects"."Name",
COALESCE("Attributes".value, (SELECT "Attributes".value FROM
"Attributes" WHERE object_id = 0))
FROM "Objects" LEFT JOIN
"Attributes" ON "Objects"."ID" = "Attributes".object_id;
Thanks,
Johnny
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-09-29 19:11 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-09-29 16:02 Need help with a special JOIN Andreas <maps.on@gmx.net>
2012-09-29 17:32 ` Samuel Gendler <sgendler@ideasculptor.com>
2012-09-29 17:33 ` David Johnston <polobo@yahoo.com>
2012-09-29 18:28 ` Victor Sterpu <victor@caido.ro>
2012-09-29 19:11 ` Johnny Winn <j.winn.v@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox