August 29, 2011 at 4:11 pm
I'm quite new to SQL, and I'm struggling to write a query that will obtain the info I want.
Here is a sample database structure:
EMPLOYEE
- employeeID (PK)
- employeeName
- associateID (the employeeID of another employee)
- groupID
- managerID (the employeeID of another employee)
GROUP
- groupID (PK)
- groupName
MANAGER
- managerID (PK)
- managerName
What if I want a query that will give me the following for a given employee?
- employeeID
- groupName
- associateName
- managerName
Obviously, I need to join the tables together, but how to I select the relevant fields?
Thank you for any help you can provide me.
August 29, 2011 at 5:43 pm
See if this helps: http://beginner-sql-tutorial.com/sql-joins.htm
There is a section on self joins towards the bottom that will help, and it includes aliases in the example for selecting fields.
PS: Under most business logic the manager would either be a self join to the employee table or a join to a manager table, not both as your sample suggests.
A self join would be the preferred option in most cases.
August 30, 2011 at 7:48 am
I may be misreading the table structure and question, but don't think this is a recursive or self-join question. I think it's a simple join.
You should be able to use something like:
SELECT
e.employeeID
g.groupName
e.associateName
m.managerName
FROM Employee e
JOIN Group g
ON e.GroupID = g.GroupID
JOIN Manager m
ON e.ManagerID = m.ManagerID
WHERE e.EmployeeID = 123 -- or whatever employee you are searching for
August 30, 2011 at 11:39 am
This is a self join exercise.
The manager name should be derived from the employee name column.
This is one example.
http://blog.sqlauthority.com/2010/07/08/sql-server-the-self-join-inner-join-and-outer-join/
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply