Re: outer join

hgary@eg570.egclassic.com (Gary Hale) wrote: : I am trying to write a SQL outer join so I can see all of the kit :components. The problem is that my where information is based on the :item & parent. For example: : select parent_kitheader, component_kitdetail : from item, kitheader, OUTER kitdetail : where id_item = kitid_kitheader and : id_item = kitid_kitdetail and : prodgrp_item = '000101' :The problem I have here is that the product group finds the item and :the kit header that is required but will not find the components of :the kit detail because of the product group ! So The above selection :will return nothing because the product groups for the :components are not '000101'. There must be another way to write the :syntax? :Anyone have any ideas? You have to have a foreign key in kitdetail referencing the primary key in kitheader and use that for the join. Something like: select parent_kitheader, component_kitdetail from item, kitheader, OUTER kitdetail where prodgrp_item = '000101' and id_item = kitid_kitheader and kitheader.primarykey = kitdetail.foreignkey if I understand your tables and requirements correctly. (It would have been much simpler if you included info on the major columns in each of the tables.) Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org