Thread: Outer join construction step :strange results

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

Howdy, Cedric .

Before analysing this problem of yours further, I 'd like you to kindly 
clarify me some points, please



>
> 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.
>


T0.id is not a primary key, is it?

> 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.
>

>
> 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.
>

T1T2 is an associative table between items and their property values, not 
types, is this correct?
If so, I am not realizing why you say that this second view will give you 
all property types. Didn't you mean property values?
Also, I am not reaching the point of the "1=1" thing. What is the propose 
of it, exactly?
Isnt that condition always truth ? Maybe I am lacking some sql knowledge ...

Thanks in advance for advising me

Best,
Oliveiros

Oliveiros d'Azevedo Cristina wrote:
> Howdy, Cedric .
>
> Before analysing this problem of yours further, I 'd like you to
> kindly clarify me some points, please
>
>
>
>>
>> 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.
>>
>
>
> T0.id is not a primary key, is it?
all columns with id are Primary keys
all columns with fk_T1 are foreign keys
>
>> 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.
>>
>
>>
>> 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.
>>
>
>
> T1T2 is an associative table between items and their property values,
> not types, is this correct?
YES
> If so, I am not realizing why you say that this second view will give
> you all property types. Didn't you mean property values?
no types because T2.fk_T3 is the foreign key to the property types T3
>
> Also, I am not reaching the point of the "1=1" thing. What is the
> propose of it, exactly?
> Isnt that condition always truth ? Maybe I am lacking some sql
> knowledge ...
yes it is allways tru is just a typo trick to have all conditions
aligned so that you can comment them out easily
>
> Thanks in advance for advising me
>
> Best,
> Oliveiros
>
You are to be thanked to getting to my problem

cheers

> Oliveiros d'Azevedo Cristina wrote:
>> Howdy, Cedric .
>>
>> Before analysing this problem of yours further, I 'd like you to
>> kindly clarify me some points, please
>>
>>
>>
>>>
>>> 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.
>>>
>>
>>
>> T0.id is not a primary key, is it?
> all columns with id are Primary keys
> all columns with fk_T1 are foreign keys


Can you specify a little more how you represent T1 item sets with this data 
model?
What exactly represents one row from table T0? A pair (id, fk_T1) ? A set 
with just one element?

Best,
Oliveiros

Oliveiros d'Azevedo Cristina wrote:
>
>
>> Oliveiros d'Azevedo Cristina wrote:
>>> Howdy, Cedric .
>>>
>>> Before analysing this problem of yours further, I 'd like you to
>>> kindly clarify me some points, please
>>>
>>>
>>>
>>>>
>>>> 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.
>>>>
>>>
>>>
>>> T0.id is not a primary key, is it?
>> all columns with id are Primary keys
>> all columns with fk_T1 are foreign keys
>
>
> Can you specify a little more how you represent T1 item sets with this
> data model?
(T0.id,T0.fk_T1)=(1,234),(1,235),(1,236)

234,235,236 all T1 items and 1 the set of them

> What exactly represents one row from table T0? A pair (id, fk_T1) ? A
> set with just one element?
one item in the set.

>
> Best,
> Oliveiros
>

>> Can you specify a little more how you represent T1 item sets with this
>> data model?
> (T0.id,T0.fk_T1)=(1,234),(1,235),(1,236)
> 234,235,236 all T1 items and 1 the set of them
>> What exactly represents one row from table T0? A pair (id, fk_T1) ? A
>> set with just one element?
> one item in the set.

That's what I thought in the first place, actually.

But then T0.id cannot be a primary key, because IIRC the primary key 
integrity constraint doesn't allow repeating of values.
And on the example you 've just posted you have three ID = 1

Ain't I Right?

Also, please, do not forget to CC to list, it's always possible that someone 
with more knowledge than me might help you faster

Best,
Oliveiros 

You are right, this is not a correct description.
I'll just in the process of creating a test db to clarify my point and
to produce the symptom.

It'ill me take me some more time, give me an hour or so

thks

cedric



Oliveiros d'Azevedo Cristina wrote:
>
>>> Can you specify a little more how you represent T1 item sets with this
>>> data model?
>> (T0.id,T0.fk_T1)=(1,234),(1,235),(1,236)
>> 234,235,236 all T1 items and 1 the set of them
>>> What exactly represents one row from table T0? A pair (id, fk_T1) ? A
>>> set with just one element?
>> one item in the set.
>
> That's what I thought in the first place, actually.
>
> But then T0.id cannot be a primary key, because IIRC the primary key
> integrity constraint doesn't allow repeating of values.
> And on the example you 've just posted you have three ID = 1
>
> Ain't I Right?
>
> Also, please, do not forget to CC to list, it's always possible that
> someone with more knowledge than me might help you faster
>
> Best,
> Oliveiros
>