Message-ID: <4C28736E.8030409@dreamgnu.com>
Date: Mon, 28 Jun 2010 12:03:26 +0200
From: ced <cedric@dreamgnu.com>
User-Agent: Mozilla-Thunderbird 2.0.0.24 (X11/20100329)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Outer join construction step :strange results
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Dear members of the list.
I've tried to summarise my problem in a synthetic manner.
Stripping non necessary columns (they only contain data about the row no
relational stuff).
Giving general names to the tables.

I have table following tables  T0,T1,T2,T3, T1T2
with
-T0(id, fk_T1)
-T1(id )
-T2(id, fk_T3)
-T1T2(fk_T1,fk_T2)
-T3 (id)

The table T0 represents sets of T1 items.
The table T1 represents items.
The table T3 represents property types.
The table T2 represents represents property values of a specific type.

An item T1 can have no, one, or more property types T3 with one or more
values T2.

For every T1 item of a T0 set I want to know if they do or do not have
T3 property types and if their values.

To build my query I begin with 2 views that I'm going to split in their
constituant later.

so I begin with view1

create view view1 as
select T3.id as all_ids
from T0,T1,T1T2,T3
where 1=1
and T0.id=12345
and T0.fk_T1=T1.id
and T1.id=T1T2.fk_T1
and T1T2.fk_T2=T2.id
and T2.fk_T3=T3.id
group by 1;

If I did no mistake this will give me all properties types present in
the T0 set with Id 12345.
Lets say this query gives me 5 rows.

next comes view2 as

create view view2 as
select T2.fk_T3 as my_id
from T2,T1T2
where 1=1
and T1T2.fk_T1=23456
and T1T2.fk_T2=T2.id;

This will give me all properties types that the T1 item with id 23456 has.
let say this query gives me 4 rows.

I know build the query that will give me the answer to my problem:

select
v1.all_ids,v2.my_id
from view1 v1 left outer join view2 v2 on v1.all_ids=v2.my_id;

This query gives me 5 rows with one wit a null value for v2.my_id this
is the expected result the join is correct.
N.B. the item T1.id=23456 is a part of the set T0.id=12345

I now create view3
create view view3 as
select T1T2.fk_T2 ,
from T1T2
where  T1T2.fk_T1=23456;

the query
select   from view3 v3, T2 where T2.id=v3.fk_T2;
gives me as expected the 4 same rows;

so now I rewrite my query:
select v1.all_ids,T2.fk_T3
from view1 v1 left outer join(
view3 v3 join T2 on v3.fk_T2=T2.id
) on v1.all_ids=T2.fk_T3;

This query gives me the expected result 5 rows with one with null values
as above.

Last step I rewrite my query

select v1.all_ids,T2.fk_T3
from view1 v1 left outer join(
T1T2 join T2 on T1T2.fk_T2=T2.id
) on v1.all_ids=T2.fk_T3
where T1T2.fk_T1=23456;

AND here it happens I only get 4 rows the one with the null values the
one where there is no property value for a property type
has dissaperead.

What do I do wrong?
is this the right way to do it.
My next step will be (once this is solved) to "defactorise" the view1.
and giving the id of T0 sets as selection parameter to generalise this
to all T0 sets.

Thank for your advice

Cedric