Outer joins

AIX 4.3 - IDS 7.31 UC5 I have a simple outer join statement that goes like this. select count(*) from table1, outer table2 where table1.field1 = <constant> and <expression1> = 0 and table1.field2= table2.field2 expression1 has nothing to do with table2 and is intended to be used as a filter yet the engine seems to treat it as part of the outer join and does not filter the rows. Why? What are the rules as to when a condtion is a join condition or just a filter.

On Tue, 02 May 2000 19:37:31 GMT, PAUL HOWARD <paulhoward@home.com> wrote: >select count(*) from table1, outer table2 >where table1.field1 = <constant> and <expression1> = 0 and table1.field2>= table2.field2 What sort of results are you getting? What sort of results are you expecting? What is that "<expression1>"? HTH, Douglas Wilson

PAUL HOWARD wrote: > > AIX 4.3 - IDS 7.31 UC5 > > I have a simple outer join statement that goes like this. > > select count(*) from table1, outer table2 > where table1.field1 = <constant> and <expression1> = 0 and table1.field2> = table2.field2 > > expression1 has nothing to do with table2 and is intended to be used as > a filter yet the engine seems to > treat it as part of the outer join and does not filter the rows. > > Why? > What are the rules as to when a condtion is a join condition or just a > filter. I do not know this one, however, using the new ANSI OUTER JOIN syntax you can isolate the join conditions from the filters and since you are running 7.31 which supports that syntax, go for it. Art S. Kagel

The Beef Man <paulhoward@home.com> writes: > I am looking at the "FROM clause" in the manual and it makes no mention of the > ANSI outer join syntax. I am looking for something like FULL|LEFT|RIGHT OUTER > JOIN <table> ON <join clause> but I can find no such beast. Do you know where > this might be documented? If you have the manual you could look up OUTER in the index;) SELECT foo FROM bar OUTER baz WHERE .... if minds serves me right? Thomas

I am looking at the "FROM clause" in the manual and it makes no mention of the ANSI outer join syntax. I am looking for something like FULL|LEFT|RIGHT OUTER JOIN <table> ON <join clause> but I can find no such beast. Do you know where this might be documented? Thanks "Art S. Kagel" wrote: > PAUL HOWARD wrote: > > > > AIX 4.3 - IDS 7.31 UC5 > > > > I have a simple outer join statement that goes like this. > > > > select count(*) from table1, outer table2 > > where table1.field1 = <constant> and <expression1> = 0 and table1.field2> > = table2.field2 > > > > expression1 has nothing to do with table2 and is intended to be used as > > a filter yet the engine seems to > > treat it as part of the outer join and does not filter the rows. > > > > Why? > > What are the rules as to when a condtion is a join condition or just a > > filter. > > I do not know this one, however, using the new ANSI OUTER JOIN syntax you > can isolate the join conditions from the filters and since you are running > 7.31 which supports that syntax, go for it. > > Art S. Kagel