Re: How to do it in SQL

On Mon, 30 Dec 1996, 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 > > > -Toby Denbow > tdenbow@mathworks.com > Tony, select parent_company_id parent,company_id co from company where company_id = 1 ; Now, I'm assuming your key is something like (company_id,parent_company_id). If it's just company_id you'll only get one instance; no others will be allowed. If your table is not keyed, no problem: you can have as many duplicates as you want. If you want to get the names of the parents, which are also stored in the company table, you'll have to do a self.join, like - select b.parent_company_id parent,b.company_name pname, a.company_id from company a,company b where a.company_id = 1 and a.company_id = b.parent_company_id ; As for the looping, try ESQL/C or 4GL (you may also achieve the same effect in stored procs, I'm not sure). Hope this helps, Nick ********************************* Nick Nobbe, Library of Congress NLS/BPH mail: nnob@loc.gov *********************************