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

Thanks Art!!

Actually it is the other way around. Want to keep all matched records
and throw away the others. But I got your point, anyway ;-))

I thought about 1), but seems to me not effectively enough.
Unfortunately the database-layout I am working on is at least as old as
the server version. That is why my query already takes 1 to 5 minutes
(just the query, without reading actual data!!!). The layout is really,
really crappy.

The second point, though was not present to me. Sounds perfect for my
situation. I already summed up some joins in two temp-tables, so if
"WHERE EXISTS" works, as well, I will be fine with that (I do not have
to read data from the outer joined table).

I suppose solution two is quicker then, right?

Thank you very much for your help.

bye,

Thomas

Art S. Kagel wrote:
> 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
>

On Jun 19, 8:18 am, Tom <some-addr...@some-place.com> wrote:
> Thanks Art!!
>
> Actually it is the other way around. Want to keep all matched records
> and throw away the others. But I got your point, anyway ;-))

But, that's just an inner, or 'normal' join! Just drop the OUTER
clause an poof! You can also try the subquery version, substituting
an EXISTS clause for the NOT EXISTS clause if you don't need any
columns from the match table. Just see which is faster. Remember
Kagel's first law of SQL:

Any SQL SELECT can be express at least 3 different ways.

Its first corollary:

Taking ANSI syntax into consideration and host languages' capability
to relieve the server of some sort and join burdens, there are at
least two more ways to get the desired results.

and its second corollary:

If you haven't tried them all, you may not be using the most efficient
method.

You can only put so much trust in the optimizer. Even one so
sophisticated as IDS's

Art S. Kagel

> I thought about 1), but seems to me not effectively enough.
> Unfortunately the database-layout I am working on is at least as old as
> the server version. That is why my query already takes 1 to 5 minutes
> (just the query, without reading actual data!!!). The layout is really,
> really crappy.
>
> The second point, though was not present to me. Sounds perfect for my
> situation. I already summed up some joins in two temp-tables, so if
> "WHERE EXISTS" works, as well, I will be fine with that (I do not have
> to read data from the outer joined table).
>
> I suppose solution two is quicker then, right?
>
> Thank you very much for your help.
>
> bye,
>
> Thomas
>
> Art S. Kagel wrote:
> > 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