Re: How to do it in SQL
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
No it does not. This looks like the classical "parts explosion"
problem, where there is a recursive master-detail relationship between
the table and itself.
The trick is to write your query so that the tape is queried twice,
looking like separate tables. The following query should (I can't test
it here, obviously) display all parent companies, grouped with their
respecive vassals.
select p.company_id, p.company_name, v.company_id, v.company_name
from company p, company v
where p.company_id = v.parent_company
order by 2, 4
--
-- Jake (FOR-loops are for wimps! ;-)
-. .-
_..-'( )`-.._
./'. '||\\\\. (\\_/) .//||` .`\\.
./'.|'.'||||\\\\|.. )o o( ..|//||||`.`|.`\\.
./'..|'.|| |||||\\`````` '`"'` ''''''/||||| ||.`|..`\\.
./'.||'.|||| ||||||||||||. .|||||||||||| ||||.`||.`\\.
/'|||'.|||||| ||||||||||||{ | }|||||||||||| ||||||.`|||`\\
'.|||'.||||||| ||||||||||||{ | }|||||||||||| |||||||.`|||.`
'.||| ||||||||| |/' ``\\||`` ''||/'' `\\| ||||||||| |||.`
|/' \\./' `\\./ \\!|\\ /|!/ \\./' `\\./ `\\|
V V V }' `\\ /' `{ V V V
` ` ` V ' ' '
+-----------------------------------------------------------+
| Diplomacy: The art of getting something off your |
| chest without losing your shirt |
+------------------------Alfred E. Neuman-------------------+