RE: Problems with an outer join. It joins a second table even it

rfurdzik: Oops, I think I misinterpreted the thrust of your question. Your real question seems to be 'why is the C.PK NOT NULL criteria being ignored?' It is my understanding that this is an Informix "limitation." From past discussions in this group I recall that your result is the correct performance according to Informix. The C.PK NOT NULL criteria is evaluated as a part of the inner join between B and C, not as a part of the outer join between A and the result of the inner join. I believe the last sentence is the answer you are looking for. I apologize for the poorly written gibberish I wrote earlier, i'm not sure what came over me! ;-) HTH, -Todd > ---------- > From: rfurdzik@paulweiss.com[SMTP:rfurdzik@paulweiss.com] > Sent: Friday, October 09, 1998 2:12 PM > To: TBernhardt@wii.com > Cc: informix-list@iiug.org > Subject: RE: Problems with an outer join. It joins a second table > even it should not, there is no records in a second for > specified criteria. > > Why join of A and B is performed at all if there is no matching > record between > B and C, this record from B table should be dropped because subset of > B and C > returns nothing for this row. My point is why B and C are not joined > first > (there should be inner join between them) and then base on this result > set join > with A should be performed. Thanks for your response. > > > > > > Todd Bernhardt <TBernhardt@wii.com> on 10/09/98 03:47:38 PM > To: Rafal Furdzik/PaulWeiss > cc: > Subject: RE: Problems with an outer join. It joins a second table even > it > should not, there is no records in a second for specified criteria. > > > > This looks correct to me: > > The join between B and C will return only the FK2 = "A" data > set--because of your C.pk in ('A') criteria. > > When you join A with the outer B and C, you will get the A.FK2 = "B" > record returned with a null C.PK because there isn't a matching C > record. > > Hope this makes sense. I don't have time at the moment to rework the > wording. > > -Todd > > > ---------- > > From: rfurdzik@paulweiss.com[SMTP:rfurdzik@paulweiss.com] > > Sent: Thursday, October 08, 1998 1:36 PM > > To: informix-list@iiug.org > > Subject: Problems with an outer join. It joins a second table > > even it should not, there is no records in a second for specified > > criteria. > > > > When you do an outer join with another table to which you applied > > criteria > > which in result returns no data, an outer join still will be > performed > > joining > > a row from first table with an empty row. Please help. > > > > > > drop table tempa; > > drop table tempc; > > create table tempA(PK Integer, FK INTEGER, FK2 Char(1)); > > create table tempC(PK Char(1)); > > INSERT INTO tempA VALUES (10, null, 'A'); > > INSERT INTO tempA VALUES (20, 10, 'B'); > > INSERT INTO tempA VALUES (30, 40, 'A'); > > INSERT INTO tempC VALUES ('A'); > > INSERT INTO tempC VALUES ('B');> > > > select A.PK, A.FK, B.PK, B.FK2, C.PK > > FROM tempA As A, OUTER (tempA As B, tempC As C) > > Where A.FK=B.PK > > AND B.FK2=C.PK > > AND C.pk is not null > > AND C.pk In('A')> > > > It is showing rows where C.pk is null an is not in 'A'. Is it a bug > > ???? Please > > help > > > > > > > > __________________________________________________ > > > > This message is intended only for the use of the > > Addressee and may contain information that is > > PRIVILEGED and CONFIDENTIAL. If you are not the > > intended recipient, you are hereby notified that > > any dissemination of this communication is > > strictly prohibited. If you have received this > > communication in error, please erase all copies of > > the message and its attachments and notify us > > immediately. Thank You. > > __________________________________________________ > > > > > > > > > > > > > > > > __________________________________________________ > > > > This message is intended only for the use of the > > Addressee and may contain information that is > > PRIVILEGED and CONFIDENTIAL. If you are not the > > intended recipient, you are hereby notified that > > any dissemination of this communication is > > strictly prohibited. If you have received this > > communication in error, please erase all copies of > > the message and its attachments and notify us > > immediately. Thank You. > > __________________________________________________ > > > > > > > > > > __________________________________________________ > > This message is intended only for the use of the > Addressee and may contain information that is > PRIVILEGED and CONFIDENTIAL. If you are not the > intended recipient, you are hereby notified that > any dissemination of this communication is > strictly prohibited. If you have received this > communication in error, please erase all copies of > the message and its attachments and notify us > immediately. Thank You. > __________________________________________________ >