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

CompanyIDCompanyName...CompanyParentID

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

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
—
2010-02-28T14:01:00Z —
#2

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
—
2010-02-28T14:16:21Z —
#3

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
—
2010-02-28T14:24:57Z —
#4

how many levels of subcompany are there?

adamslade
—
2010-02-28T20:44:28Z —
#5

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.