Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A very tricky query--please help


A very tricky query--please help

Author
Message
Mark-459099
Mark-459099
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
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.
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 Visits: 5314
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
Mark-459099
Mark-459099
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
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.
matt6288
matt6288
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1208 Visits: 1350
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?
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 Visits: 5314
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
Mark-459099
Mark-459099
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
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?
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 Visits: 5314
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
Mark-459099
Mark-459099
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 138
thanks Elliot, you are correct. Sorry I didn't acknowledge your query.
Mark-459099
Mark-459099
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
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.
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6208 Visits: 5314
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search