t-sql

Question

t-sql

weston_086

SSCommitted

Points: 1645
More actions
February 6, 2012 at 1:21 pm

#391484

Write SQL using the employee table below to create a data set listing the manager id, the manager name, the employee id and the employee name
Employee table – t_emp
emp_idemp_namemanager_id
1Jane
2Fred3
3Alan1
4Sue1
5Bill1
Data set that should be returned:
manager_idmanager_nameempoyee_idempoyee_name
3Alan2Fred
1Jane3Alan
1Jane4Sue
1Jane5Bill

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 1

Sounds like a homework problem, but just join the table to itself on emp_id from the first table to manager_id on the second and pick the columns you need.

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 2

For the best help its always better to provide some scripts to create some smaple data however, looking at what you wrote all you need to do is inner join on the 2 tables for example

select manager_id manager_name empoyee_id empoyee_name

from t_emp a inner join what_ever_your_other_table_is_called on a.manager_ID=b.manager_ID

***The first step is always the hardest *******

Jared SSC Guru Points: 61793 More actions · Answer 3

weston_086 (2/6/2012)
Write SQL using the employee table below to create a data set listing the manager id, the manager name, the employee id and the employee name
Employee table – t_emp
emp_idemp_namemanager_id
1Jane
2Fred3
3Alan1
4Sue1
5Bill1
Data set that should be returned:
manager_idmanager_nameempoyee_idempoyee_name
3Alan2Fred
1Jane3Alan
1Jane4Sue
1Jane5Bill

SELECT m.emp_id AS manager_id, m.emp_name AS manager_name,

e.emp_id AS employee_id, e.empname AS employee_name

FROM t_emp e

LEFT JOIN t_emp m

ON e.manager_id = m.emp_id

Try the above. You are joining the table to itself. However, what if someone is the top manager or does not have one assigned? The left join here will include that employee, but you may want some type of code to fill in the nulls.

Jared
CE - Microsoft