Date: Fri, 28 Apr 2006 06:46:06 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: emils.klotins@gmail.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Outer joins?
In-Reply-To: <9dcb6fa40604280623h6f353153y@mail.gmail.com>
Message-ID: <20060428063323.R10520@megazone.bigpanda.com>
References: <9dcb6fa40604280623h6f353153y@mail.gmail.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

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.