Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

A very tricky query--please help Expand / Collapse
Author
Message
Posted Monday, September 14, 2009 8:52 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 28, 2009 6:33 PM
Points: 59, Visits: 138
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.
Post #787859
Posted Monday, September 14, 2009 9:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #787864
Posted Tuesday, September 15, 2009 5:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 28, 2009 6:33 PM
Points: 59, Visits: 138
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.
Post #788073
Posted Tuesday, September 15, 2009 6:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, December 8, 2014 12:25 PM
Points: 1,207, Visits: 1,278
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?
Post #788081
Posted Tuesday, September 15, 2009 6:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #788089
Posted Thursday, September 17, 2009 10:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 28, 2009 6:33 PM
Points: 59, Visits: 138
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?
Post #789790
Posted Thursday, September 17, 2009 12:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #789904
Posted Thursday, September 17, 2009 2:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 28, 2009 6:33 PM
Points: 59, Visits: 138
thanks Elliot, you are correct. Sorry I didn't acknowledge your query.
Post #789976
Posted Thursday, September 17, 2009 2:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 28, 2009 6:33 PM
Points: 59, Visits: 138
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.
Post #789979
Posted Thursday, September 17, 2009 3:02 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #789999
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse