Re: OUTER JOINS in Informix SQL

Informix did not support the SQL92 syntax for outer joins until IDS7.31. The Informix specific syntax is below. The only limitation to the Informix syntax is that the lack of the ON join clause does not permit one to filter the results to exclusively include those rows that have no match. Lance King wrote: > > I am trying to run a SQL query against an Informix DB (INFORMIX 5.10.UC1) > from ASP. Normal queries work fine, but when I try to do an outer join I > get syntax errors. I have tried several different flavors of outer joins > (i.e. the ANSI version, the "*=" version). Can someone please help? > > Here is the sql statement (ANSI Version): > > SELECT > tblTemplateHeader.Name AS TemplateName, > tblTemplateHeader.ID AS TemplateID, > tblTemplateItem.ItemID AS ItemID, > tblTemplateItem.Sequence AS LineNumber, > tblItem.ItemCode AS ItemCode, > tblItem.ItemDscr AS ItemDscr > FROM tblTemplateHeader, OUTER (tblTemplateItem, OUTER tblItem) WHERE tblTemplateHeader.ID IN (88) AND tblTemplateItem.TemplateID = tblTemplateHeader.ID AND tblItem.ID = tblTemplateItem.ItemID ORDER BY tblTemplateHeader.TemplateName, tblTemplateItem.Sequence; Note the placement of the parenthesis indicating the nesting of OUTER joins. Art S. Kagel > ( > tblTemplateHeader > LEFT OUTER JOIN tblTemplateItem > ON tblTemplateItem.TemplateID = tblTemplateHeader.ID ) > LEFT OUTER JOIN tblItem > ON tblItem.ID = tblTemplateItem.ItemID > WHERE tblTemplateHeader.ID IN (88) > ORDER BY > tblTemplateHeader.TemplateName, > tblTemplateItem.Sequence > > The query actually involves more LEFT OUTER JOINS, but I just wanted to > simplify it here. Basically, if an item ID exists in tblTemplateItem but > doesn't exist in tblItem, I still want a record returned with null values > for ItemCode and ItemDscr. (That is a "LEFT OUTER JOIN", isn't it?) > > I also tried: > SELECT > tblTemplateHeader.Name AS TemplateName, > tblTemplateHeader.ID AS TemplateID, > tblTemplateItem.ItemID AS ItemID, > tblTemplateItem.Sequence AS LineNumber, > tblItem.ItemCode AS ItemCode, > tblItem.ItemDscr AS ItemDscr > FROM > ( > tblTemplateHeader, > tblTemplateItem, > tblItem > WHERE tblTemplateHeader.ID IN (88) > AND tblTemplateItem.TemplateID *= tblTemplateHeader.ID > AND tblItem.ID *= tblTemplateItem.ItemID > ORDER BY > tblTemplateHeader.TemplateName, > tblTemplateItem.Sequence > > Either case, I get syntax errors. I tried several versions of each of the > above with no luck. A "plain" query without trying to do the LEFT OUTER > JOIN works fine. > Any help will be greatly appreciated. > > Lance > lance.king@definition6.com