From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: emils.klotins@gmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Outer joins?
Date: Fri, 28 Apr 2006 06:46:06 -0700 (PDT)
Message-ID: <20060428063323.R10520@megazone.bigpanda.com> (raw)
In-Reply-To: <9dcb6fa40604280623h6f353153y@mail.gmail.com>
References: <9dcb6fa40604280623h6f353153y@mail.gmail.com>
On Fri, 28 Apr 2006, Emils wrote:
> I am trying to do simple self-joins.
>
> The table structure is:
>
> object_values
> ==========
> obj_id
> att_id
> value
>
> namely, each object can have arbitrary number of attributes each of
> them with a value.
>
> What I want, is a simple table of objects with some of their specific
> attributes, the result should be in form:
>
> obj_id1 o1att1_value o1att2_value o1att3_value
> obj_id2 o2att1_value o2att2_value o2att3_value
> ...
>
> Obviously, if eg obj2 doesn't have att2 in the table, I want a NULL in
> that grid point.
>
> So, I thought some nested outer joins should be OK?
>
> SELECT
> OV.obj_id AS obj_id,
> OV.value AS NAME,
> ov1.value AS DESCRIPTION,
> ov2.value AS ICON
> FROM
> object_values OV LEFT JOIN object_values ov1 USING(obj_id)
> LEFT JOIN object_values ov2 USING(obj_id)
> WHERE OV.att_id=7 AND ov1.att_id=8 AND ov2.att_id=16;
AFAIK, effectively first the join happens then the where filter. So,
imagine the output of the joins without any where clause and then apply
the where clause as a filter upon that. Even if you got NULL extended
rows, you'd filter them out because the ov1.att_id and ov2.att_id tests
would filter them out. In addition, you won't actually get NULL extended
rows I think, because there will always be at least one row with matching
obj_id (the one from ov that's being worked on).
I think putting a test in an ON clause associated with the join (using
something like ... left join object_values ov1 ON(ov.obj_id = ov1.obj_id
and ov1.att_id=8) rather than where will consider both as part of the join
and null extend even if there are obj_id matches if none of those have
att_id=8.
Another way of doing the same thing is using subselects in from to filter
the right hand tables you wish to join.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, emils.klotins@gmail.com
Subject: Re: Outer joins?
In-Reply-To: <20060428063323.R10520@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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