Re: How to do it in SQL

Thanks to all who replied. Unfortunately my suspisions were confirmed, it cannot be done without using a LOOP or without creating a cross-reference table. I think I will do the SPL route. Thanks again, -Toby Denbow Mark D. Stock wrote: > > Toby Denbow wrote: > > > > Say i have a table called 'company' with the following fields: > > company_id > > company_name > > parent_company_id > > > > How can I get a listing of all of a Companies Parents without using a > > FOR loop? > > > > Sample DATA: > > Record 1: > > company_id = 1 > > company_name = Company one > > parent_company_id = 2 > > > > Sample DATA: > > Record 2: > > company_id = 2 > > company_name = Company two > > parent_company_id = 3 > > > > Sample DATA: > > Record 3: > > company_id = 3 > > company_name = Company three > > parent_company_id = NULL > > > > I want to execute a select that gives me BOTH of the parents for company > > _id = 1 > > Mmmm, this is an interesting problem, recursion in SQL. I don't think it > can be done. I guess you have got the first parent like so: > > SELECT company_name, parent.company_name parent_name > FROM company, company parent > WHERE company.parent_company_id = parent.company_id > AND company.company_id = 1> > But then you want the parent's parent and so on. I think the only thing > you could do here is to know the maximum number of parents and code them > as SELF-OUTER joins. An alternative would be to write it in SPL. > > Hope that helps, > -- > > --------------------------------------------------------------- > Mark. > > +------------------------------------------------------------------+ > |Mark D. Stock - The West Solutions Group http://www.west.co.za | > | The Informix FAQ is at http://www.iiug.org | > |mailto:marks@west.co.za +-----------------------------------------+ > |Tel: +27 11 803 2151 |If it doesn't work, force it! | > |Fax: +27 11 803 2189 |If it breaks, it needed replacing anyway!| > |Cell: +27 83 250 2325 |Well, that's how I code anyway! | > +------------------------+-----------------------------------------+