employees in both a company and sub-company

[sfullman]

I have a company table with three main fields
ID
CompanyName
ParentID

if parentid is not null, then that company is a child company of a parent company. so far so good.

now, there's another table of employees/staff, and staff can be part of multiple companies. just for the same of this post assume it has
ID
FirstName,
LastName

I also have a join table CompanyEmployee with a compound primary key

CompanyID
EmployeeID
Position (not part of the primary key)

my question relates to elegance. What is the most effective way to get a list of companies and employees, and their position in the company? If there was just one level of company, this would be easy, but we have to look at both company.ID and company.ParentID when tying to the CompanyEmployee table.

I could hack this with DISTINCT but you guys often give great feedback that helps me learn something.. those of you who do so know who you are 🙂

Thanks,
Samuel

[ixalmida]

How I would do it:

SELECT Position, LastName, FirstName, CompanyName
FROM CompanyEmployees 
  JOIN Companies ON CompanyEmployees.CompanyID = Companies.ID 
  JOIN Employees ON CompanyEmployees.EmployeeID = Employees.ID
GROUP BY CompanyName, LastName, FirstName, Position

The "GROUP BY" serves the dual-purpose of weeding out duplicates as well as grouping the employees by company. If you mean to include parent companies, then you probably want to use a sub-query on the companies table to get the parent company names and then group by the resulting parent company name.