Outer join construction step :strange results
От | ced |
---|---|
Тема | Outer join construction step :strange results |
Дата | |
Msg-id | 4C28736E.8030409@dreamgnu.com обсуждение исходный текст |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: