From: Emils <gnudiff@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Outer joins?
Date: Fri, 28 Apr 2006 16:23:53 +0300
Message-ID: <9dcb6fa40604280623h6f353153y@mail.gmail.com> (raw)
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
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: gnudiff@gmail.com
Subject: Re: Outer joins?
In-Reply-To: <9dcb6fa40604280623h6f353153y@mail.gmail.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