Re: Outer joins?

Hello!

I am a longtime postgres user (started around 10 years ago), however,
as for some years I've been using it mostly as administrator.

Now that I have started a project and doing some SQL, I've come up
something I don't believe is right. Maybe I am too rusty on my SQL -
if so, please forgive me, but I checked it and my reasoning seemed ok
to me.

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;

So, I figured this should get me all objects that have atttribute 7
defined, regardless of whether the other attributes exist for them?

However, for some reason PG8.1 is giving me something like an INNER
join on this query - namely ONLY rows where ALL the attributes ARE
present.

Am I doing something wrong? As I said my SQL is rusty, but this looked
pretty straightforward to me...

Thanks in advance,
Emils

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.

Emils <gnudiff(at)gmail(dot)com> writes:
> 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
> ...

This isn't an outer-join problem, it's a crosstab problem. Try the
crosstab functions in contrib/tablefunc.

regards, tom lane