September 14, 2009 at 8:52 pm
I have a table as follows
id firstname lastname
1 Jay Jones
2 Nancy Cartwright
3 Carlos Ruiz
4 Steve Shepperson
The other table is as follows
Manager Supervisor Employee
1 2 4
3 1 2
2 3 1
The numbers of course represent the people in the first table. The problem is how can I create a query where I can get the name of the employee and supervisor when the manager is 1, for example. 1 stands for Jay Jones. I have some idea but I'm not quite sure. Any help will be appreciated.
September 14, 2009 at 9:10 pm
As I understand your question
SELECT SupervisorFirstName = e1.firstname,
SupervisorLastName = e1.lastname,
EmployeeFirstName = e2.firstname,
EmployeeLastName = e2.lastname
FROM dbo.staffhierarchy h,
dbo.employee e1,
dbo.employee e2
WHERE h.supervisorid = e1.id
AND h.employeeid = e2.id
AND h.managerid = 1
CEWII
September 15, 2009 at 5:44 am
Let me be clearer.
The first table is just the employee table. the second table is the relationship between the people in the first table. What I would like to do is be able to query the database so that I could get the name of the supervisor or employee of the manager whose id is 1. How can I join these two tables to reflect this.
September 15, 2009 at 6:08 am
I think this will work but it would help if you posted the DDL for these tables and some insert statements.
select firstName, lastName
from table_1
where id in (select supervisor from table_2 where manager = 1)
union
select firstName, lastName
from table_1
where id in (select employee from table_2 where manager = 1)
This will get you a set. Did you need to get these results in 1 row?
September 15, 2009 at 6:28 am
Mark (9/15/2009)
Let me be clearer.The first table is just the employee table. the second table is the relationship between the people in the first table. What I would like to do is be able to query the database so that I could get the name of the supervisor or employee of the manager whose id is 1. How can I join these two tables to reflect this.
The query I posted gives you the supervisor and employee for everyone who has a manager id of 1. I guess I'm not seeing this as that complex..
CEWII
September 17, 2009 at 10:03 am
Yes, I do need to get this in one row, so that the result set will be
Supervisor
Joshua Miller
Employee
Raymond Greer
Can you get this?
September 17, 2009 at 12:23 pm
Mark,
I am having a problem here.. I looked at Matt's query and it works almost the same as mine. You state that you want one row, while your sample data would allow for that if you added a 5th employee that was a supervisor reporting to manager and a 6th employee reporting to the 5th employee, you would get 2 rows. My query gives it to you on one row, Matt's query gives you it on two. Let me state the full problem as I see it.
1. You have a table that includes all employees, whether they are managers, supervisors, or just staff.
2. You have a link table that defines the relationships between the employees in the first table, this link table is able to support a three level hierarchy (and only three levels), Manager at the top, supervisor in the middle, and staff at the bottom.
3. For a given ManagerId in the link table you want to show the supervisor employee and the staff member, you want both items in the same row, and you only want a single row.
Now, problems I see.
1. Item 1, looks great, very common implementation..
2. Item 2, usually implemented differently since this implementation limits you to a three level hierarchy, it is often implemented as Id and Parent Id, so you have to traverse the list to get all the levels, but it can support many levels.
3. Item 3, presents a problem because with only slightly different sample data, the addition of a supervisor and a staff member reporting to manager one guarantees a second row without additional where clause items.
Is my analysis of the table layout or the desired output incorrect?
If so then the query I presented is the solution.
If not explain which item is incorrect and give an/another example if possible.
CEWII
September 17, 2009 at 2:06 pm
thanks Elliot, you are correct. Sorry I didn't acknowledge your query.
September 17, 2009 at 2:11 pm
By the way, do you know how to add multiple conditions to a IF NOT EXISTS query. I need to check two conditions instead of one. I first wrote:
IF NOT EXISTS (SELECT * FROM FRIENDS WHERE Person=@profileid AND Friend=@sid) OR (SELECT * FROM FRIENDS WHERE Person=@sid AND Friend=@profileid)
INSERT INTO Friends (Person, Friend) VALUES (@profileid, @sid)
INSERT INTO Friends (Person, Friend) VALUES (@sid, @profileid)
I then realize you can't use OR in this context. Should a comma be used between them or write two "IF NOT EXISTS" statements?
Any help would be appreciated.
September 17, 2009 at 3:02 pm
Actually you can do nearly anything that can generate a true/false condition.. You often use () to set up which goes with which comparison..
Try this:
IF NOT EXISTS ( SELECT 'X' FROM dbo.SomeTable WHERE SomeField = SomeValue )
AND NOT EXISTS ( SELECT 'X' FROM dbo.SomeOtherTable WHERE SomeField = SomeValue )
BEGIN
-- Do some work here that you want to do
END
Its a little pseudo codish but I think it shows what you are asking for..
Or you can mix it up some..
IF NOT EXISTS ( SELECT 'X' FROM dbo.SomeTable WHERE SomeField = SomeValue )
AND @SomeVariable IS NOT NULL
BEGIN
-- Do some work here that you want to do
END
CEWII
November 27, 2012 at 9:24 am
select manager,supervisor,(n1.firstname+' '+n1.lastname)employee
from employee_new n1
inner join
(
select manager,(n.firstname+' '+n.lastname)supervisor,employee from relation r
inner join employee_new n
on n.id=r.supervisor
where r.manager=1
)r1
on n1.id=r1.employee
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply