Re: Please Help with outer join syntax...

Steven Wong wrote: > How would one write an outer join query that does the following: > > Four Tables A, B, C, D. > > A left joins to B > B left joins to C > B left joins to D > > Thanks for your feedback! > > Steve Steve, I've had to experiment with outer in the past to get the desired behavior that I wanted. From what I have been able to discern outer can be inclusive or exclusive. The suggestions for outer syntax that others have supplied in their replies I find result in an exclusive join, i.e., if no data is found in the joining table, no data is returned. The following example from running code will produce a null field if no data is found in the outer join table. The purpose here is to return a complete record even if there is null data in some tables. If anyone else on this list has any suggestions on this subject I would welcome it. Informix documentation on outer joins is very terse and definitely is not ANSI compliant. This is a DBI/DBD embedded call in perl, hence the ? placeholder. select ta.trans_id, ta.user_id, pf.phone_firm, ff.fax_firm, ef.email_firm, uc.cellph,up.pager from trans_authz ta, outer phones_firm pf, outer faxes_firm ff, outer email_firm ef, outer user_cellph uc, outer user_pager up where ta.trans_id=? and ta.user_id=pf.user_id and ta.user_id=ff.user_id and ta.user_id=ef.user_id and ta.user_id=uc.user_id and ta.user_id=up.user_id ------- David M. Davisson davisson@emuni.com

Actually David the Informix syntax you use for outer join IS ANSI compliant, it is just compliant with the oldest ANSI spec for outer join. Note, however, that the latest versions of Informix, IDS 7.31 and later, support the ANSI 92 syntax including the "ON" clause which permits filtering on the NULL results columns. By the way, any query that returns no rows if the dependent table has not matching row is an INNER join NOT an OUTER join. Outer joins, by definition, return a NULL row for any row not matched. Art S. Kagel "David M. Davisson" wrote: > > Steven Wong wrote: > > > How would one write an outer join query that does the following: > > > > Four Tables A, B, C, D. > > > > A left joins to B > > B left joins to C > > B left joins to D > > > > Thanks for your feedback! > > > > Steve > > Steve, > > I've had to experiment with outer in the past to get the desired > behavior that I wanted. From what I have been able to discern outer can > be inclusive or exclusive. The suggestions for outer syntax that others > have supplied in their replies I find result in an exclusive join, i.e., > if no data is found in the joining table, no data is returned. The > following example from running code will produce a null field if no data > is found in the outer join table. The purpose here is to return a > complete record even if there is null data in some tables. If anyone > else on this list has any suggestions on this subject I would welcome > it. Informix documentation on outer joins is very terse and definitely > is not ANSI compliant. This is a DBI/DBD embedded call in perl, hence > the ? placeholder. > > select ta.trans_id, > ta.user_id, > pf.phone_firm, > ff.fax_firm, > ef.email_firm, > uc.cellph,> up.pager > from trans_authz ta, > outer phones_firm pf, > outer faxes_firm ff, > outer email_firm ef, > outer user_cellph uc, > outer user_pager up > where ta.trans_id=? > and ta.user_id=pf.user_id > and ta.user_id=ff.user_id > and ta.user_id=ef.user_id > and ta.user_id=uc.user_id > and ta.user_id=up.user_id > > ------- > David M. Davisson > davisson@emuni.com