mysql circular table query

[adamslade]

Hi,

Can anyone help me create a query that will work the following:-

I need to be able to look up a company AND their parent company from the following table. Only some companies have a parent company, others that do not have a parent have themselves listed as the parent.

Table Structure is along the lines of

CompanyID
CompanyName
...
CompanyParentID

The CompanyParentID is linked to the CompanyID, and both are further linked to other tables in the database.

Example data:-
CompanyID | CompanyName | ... | CompanyParentID
1 | ABC and Co | ... | 1
2 | XYZ and Co | ... | 2
3 | Alphabet Co | ... | 2
...

Example results:

CompanyName | ParentName
ABC and Co | ABC and Co
XYZ and Co | XYZ and Co
Alphabet Co | XYZ and Co

I have managed to learn my way around linking two or more tables, but not sure how to do this when the tables are in fact the same table.

Thanks,

Adam (slightly past newbie)

[r937]

this is a simple self-join

SELECT this.CompanyName 
     , parent.CompanyName AS ParentName
  FROM companies AS this
LEFT OUTER
  JOIN companies AS parent
    ON parent.CompanyID = this.CompanyParentID

i think it's a little unusual for a company to be its own parent

more common is for a company that does not have a parent to have NULL in the CompanyParentID column

[adamslade]

Thank you for such a fast response.

It is a unusual structure, it is set up because the ultimate parent gets billed for the works of child companies/clients, and obviously work done for the a top-level company still needs billing but to themselves!

Adam

[r937]

how many levels of subcompany are there?

[adamslade]

So far only 1 and its mainly used to cater for when a customer gives us work for their client but they pay, or when we do work for a secondary address or department.

Have you a better way of doing things?

Its important that someone can pull up a list of billable work whoever it is for, so that it gets invoiced on one invoice. it also contains a lot of other company information like addresses, telephone numbers, emails, contacts and my original idea was not to duplicate storage of this information.

[r937]

i would set CompanyParentID to NULL if the company has no parent

[adamslade]

thanks, I'll look at the likelihood of wanting to see any chain

appreciate the input