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.
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
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!
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.
