Need help in a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help in a query

Author

Topic

royc
Yak Posting Veteran

53 Posts

Posted - 2008-04-23 : 09:03:40

Hello,

I have a table of employee with these fields in it:
EMP_ID -> The employee ID
EMP_FIRSTNAME -> The employee first name
EMP_LASTNAME -> The employee last name
EMP_MANAGER -> The employee's manager ID

The manager is himself an employee therefore EMP_MANAGER contains one of the valid ID's in the employees table.

I would like to get a report of all the employees and their line managers in this format:
Employee ID, Employee first name, Employee last name, Manager ID, Manager first name, Manager last name.

How do I do it in one go without going back to the table each time I need the line manager's first and last name?

Thank you in advance.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-23 : 09:23:32

select EmployeeID = e.emp_id, Employeefirstname = e.emp_firstname, Employeelastname = e.emp_lastname,
ManagerID = m.emp_id, Managerfirstname = m.emp_firstname, Managerlastname = m.emp_lastname
from employees e
join employees m
on e.emp_manager = m.emp_id

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

royc
Yak Posting Veteran

53 Posts

Posted - 2008-04-23 : 09:40:23

Thank you very much

royc
Yak Posting Veteran

53 Posts

Posted - 2008-04-25 : 09:08:58

But what if not each employee has got a manager id?
In this case INNER JOIN will fail.
I tried using LEFT JOIN instead but I'm getting the records with manager id twice (the ones with no manager id I'm getting only once as I expect).
Any ideas...?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 09:35:12

What did you try?

SELECT		e.Emp_ID AS EmployeeID,
		e.Emp_FirstName AS EmployeeFirstName,
		e.Emp_LastName AS EmployeeLastName,
		m.Emp_ID AS ManagerID,
		m.Emp_FirstName AS ManagerFirstName,
		m.Emp_LastName AS ManagerLastName
FROM		Employees AS e
LEFT JOIN	Employees AS m ON m.Emp_ID = e.Emp_Manager

E 12°55'05.25"
N 56°04'39.16"

royc
Yak Posting Veteran

53 Posts

Posted - 2008-04-25 : 09:44:42

Well, honestly it's a bit more complicated that that.
There is a third table that contains an empid and some other details abount the employee, let's call it Global. That's the main table for the query.
I want to join the data in this table with the data in employee - his first name, last name, manager id and the manager's first and last name. This data is sitting in the Employees table as explained before.

Thank you in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 09:46:23

Neither NR nor I could have anticipated that, could we?
Did you think telling us this important piece of information was not vital to solve your problem?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 09:48:09

[code]SELECT e.Emp_ID AS EmployeeID,
e.Emp_FirstName AS EmployeeFirstName,
e.Emp_LastName AS EmployeeLastName,
eg.SomeCol,
m.Emp_ID AS ManagerID,
m.Emp_FirstName AS ManagerFirstName,
m.Emp_LastName AS ManagerLastName,
mg.SomeCol
FROM Employees AS e
LEFT JOIN Employees AS m ON m.Emp_ID = e.Emp_Manager
LEFT JOIN Global AS eg ON eg.SomeID = e.Emp_ID
LEFT JOIN Global AS mg ON mg.SomeID = m.Emp_ID[/code]

E 12°55'05.25"
N 56°04'39.16"

royc
Yak Posting Veteran

53 Posts

Posted - 2008-04-25 : 09:53:22

Hi Peso,

Apologies for that, of course you are right but this came as a surprise to me as well, I was just told that there is a third table I need to take data from.
I don't think I'm quite following your example so let me provide you with mine so tht you can correct me. Like I said, Global is the main table I'm querying from, so:

SELECT
A.Emp_ID AS EmployeeID,
A.Phone AS EmployeePhone,
B.Emp_FirstName AS EmployeeFirstName,
B.Emp_LastName AS EmployeeLastName,
C.Emp_ID AS ManagerID,
C.Emp_FirstName AS ManagerFirstName,
C.Emp_LastName AS ManagerLastName
FROM Global As A
LEFT JOIN Employees As B ON A.Emp_ID = B.Emp_ID
LEFT JOIN Employees As C ON B.Emp_Manager = C.Emp_ID

Can you please tell me why am I getting duplicates in this query?

royc
Yak Posting Veteran

53 Posts

Posted - 2008-04-25 : 10:52:15

Anyone...?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-25 : 10:59:30

Not without proper and accurate sample data.
Do you expect us to be wizard to know exactly what is stored in your tables?

E 12°55'05.25"
N 56°04'39.16"

royc
Yak Posting Veteran

53 Posts

Posted - 2008-04-25 : 11:05:56

You were able to create your query without "accurate sample data".
I thought that the query I've posted is self explanatory enough but anyway, the Global table holds Emp_ID and Emp_phone (and other fields which are not relevant to the question).
Emp_ID can be joined with Emp_ID in the Employees table and I have already detailed the Employees table before.
Assume that I must query from Global table and join it with Employees table to get the results and not the other way around.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-25 : 11:38:05

Post some sample data from your tables and we will jhelp you out.

royc
Yak Posting Veteran

53 Posts

Posted - 2008-04-25 : 11:45:00

Guys,

Thank you very much for your help but I think I got it on my own already.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources