adamslade — 2010-02-28T08:41:49-05:00 — #1
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
The CompanyParentID is linked to the CompanyID, and both are further linked to other tables in the database.
CompanyID | CompanyName | ... | CompanyParentID
1 | ABC and Co | ... | 1
2 | XYZ and Co | ... | 2
3 | Alphabet Co | ... | 2
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.
Adam (slightly past newbie)
r937 — 2010-02-28T09:01:00-05:00 — #2
this is a simple self-join
, parent.CompanyName AS ParentName
FROM companies AS this
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-28T09:16:21-05:00 — #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!
r937 — 2010-02-28T09:24:57-05:00 — #4
how many levels of subcompany are there?
adamslade — 2010-02-28T15:44:28-05:00 — #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.
r937 — 2010-02-28T17:12:43-05:00 — #6
i would set CompanyParentID to NULL if the company has no parent
adamslade — 2010-02-28T18:02:00-05:00 — #7
thanks, I'll look at the likelihood of wanting to see any chain
appreciate the input