Re: How to do it in SQL
This is a multi-part message in MIME format.
--------------714F4CF15896
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
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,
--
--------------714F4CF15896
Content-Type: text/plain; charset=us-ascii; name="ATT_SIG.TXT"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="ATT_SIG.TXT"
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! |
+------------------------+-----------------------------------------+
--------------714F4CF15896--