exclude lines after outer join, like left joins in 7.24

Hi there, I wonder if it is possible to exclude selected lines in i.e a where-statement after an outer join in informix SE 7.24. I did some bigger queries with outer-joins, but I am not able to exclude whole lines when relating to one of the joined columns. The not outer-Joined columns, the whole lines are keept. Is it possible to simulate some kind of Left-Join where these lines would have been deleted? That is very annoying. I know it is an old server version, but I do not have a choice. Thanks for your help, honestly ;-) Thomas

On Jun 18, 1:46 pm, Tom <some-addr...@some-place.com> wrote: > Hi there, > > I wonder if it is possible to exclude selected lines in i.e a > where-statement after an outer join in informix SE 7.24. > > I did some bigger queries with outer-joins, but I am not able to exclude > whole lines when relating to one of the joined columns. The not > outer-Joined columns, the whole lines are keept. > > Is it possible to simulate some kind of Left-Join where these lines > would have been deleted? > > That is very annoying. I know it is an old server version, but I do not > have a choice. Sounds like what you want is to eliminate the records which did find a match in the OUTER table so that you only return those that did not have a match. Of course in later versions, you could perform the OUTER join using ANSI syntax and join in the ON clause and filter out the non-NULL join results in the WHERE clause. In 7.24, with only Informix syntax OUTER joins, you cannot do so directly. There are, however, two ways to do this indirectly: 1) SELECT ... FROM tab1, OUTER tab2 WHERE ... INTO TEMP fred; SELECT <tab1 columns> FROM fred WHERE tab2.col IS NULL; 2) Realize that you don't need to select anything from the OUTER table and do this as a sub-query: SELECT tab1.* FROM tab1 WHERE NOT EXISTS ( SELECT 1 FROM tab2 WHERE tab1.keys = tab2.keys ); Art S. Kagel