DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=pTzQAGyyoknAur9nwRa0T79YNc5qFkzp1txE5X8Vd86SW6bNgzjcGQzPnwNUVjTqmi5ctw3erXhoZq0Rt7/PhgX/b7wFSJrsMF35inn5m/ejtnrsH57yYJb8ZVINZKWA2lzohnMLqy66cxmUVI7fyp6HPjv+K7LHHJB+IuMCH4o=
Message-ID: <9dcb6fa40604280623h6f353153y@mail.gmail.com>
Date: Fri, 28 Apr 2006 16:23:53 +0300
From: Emils <gnudiff@gmail.com>
Reply-To: emils.klotins@gmail.com
To: pgsql-sql@postgresql.org
Subject: Outer joins?
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
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)
=09LEFT JOIN object_values ov2 USING(obj_id)
WHERE OV.att_id=3D7 AND ov1.att_id=3D8  AND ov2.att_id=3D16;

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