Self Join

Question

Self Join

farooq.hbs

SSCrazy

Points: 2083
More actions
May 6, 2012 at 1:12 am

#273579

Hi all
In the Employee Table, (EMPNo,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
Example DAta
EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK790217-Dec-8080020
7499ALLENSALESMAN769820-Feb-81160030030
7521WARDSALESMAN769822-Feb-81125050030
7566JONESMANAGER78392-Apr-81297520
7654MARTINSALESMAN769828-Sep-811250140030
7698BLAKEMANAGER78391-May-81285030
7782CLARKMANAGER78399-Jun-81245010
7788SCOTTANALYST75669-Dec-82300020
7839KINGPRESIDENT17-Nov-81500010
Question:
Get all the employees who work in the same departments as of "SCOTT"
I used this query but not working to fetch the reslut set of query
Select E.empno,E.emp_name, E1.Emp_name as ManagerName from Employee E join Employee E1 on
E.mgr= E1.Emp_no and E.Emp_no != E1.Emp_no

Viewing 7 posts - 1 through 6 (of 6 total)

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

Dave Brooking Ten Centuries Points: 1373 More actions · Answer 1

I really don't think you have tried the solution you are proposing as the columns in the table definition do not match those in the query. Can you explain what you think your query is doing?

You do not need a self join for the solution you can use a subquery, or even a simple

where deptno = 20

Your proposed query has something to do with a departmental hierarchy, which isn't necessarily part of the question you asked.

Dave

vinu512 SSCoach Points: 15729 More actions · Answer 2

This might work:

Select * From Ex

Where DEPTNO In (Select DEPTNO From Ex Where ENAME = 'SCOTT')

Edit:

If you Don't want Scott in the result set then you can edit the above query as follows:

Select * From Ex

Where DEPTNO In(Select DEPTNO From Ex Where ENAME = 'SCOTT')

And ENAME <> 'SCOTT'

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

Jan Van der Eecken SSCrazy Eights Points: 9029 More actions · Answer 3

CELKO (5/6/2012)
What you did post was not normalized. Jobgs are entities, personnel are entities and they have a relationship. Here is a skeleton that you can use to do it right: ...

@joe, I totally agree with you. Obviously this is an exam or assignment question, so whoever put this question together should have at least had the decency to normalize that table, so don't blame it on the OP. I'd suggest the OP quits that course and enroll in one run by a decent institution. :w00t:

@Farooq, Dave's and Vinu's hints should enable you to answer your question. Come back if you get stuck nevertheless.

--------------------------------------------------------------------------
A little knowledge is a dangerous thing (Alexander Pope)
In order for us to help you as efficiently as possible, please read this before posting (courtesy of Jeff Moden)[/url]

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 4

vinu512 (5/7/2012)
This might work:
Select * From Ex
Where DEPTNO In (Select DEPTNO From Ex Where ENAME = 'SCOTT')
Edit:
If you Don't want Scott in the result set then you can edit the above query as follows:
Select * From Ex
Where DEPTNO In(Select DEPTNO From Ex Where ENAME = 'SCOTT')
And ENAME <> 'SCOTT'

+1

Sony Francis @EY SSCommitted Points: 1539 More actions · Answer 5

I have a little confusion about your question. I think you are expecting the result of below query

Select E.empno,E.emp_name, E1.Emp_name as ManagerName from Employee E join Employee E1 on

E.mgr= E1.Emp_no

Where E. DEPTNO =( Select DEPTNO From Employee Where ENAME = 'SCOTT').

If this will not help you then pls clear the question

thinesh.mscss SSC Rookie Points: 49 More actions · Answer 6

SELECT E.empname as Employee, M.empname as Manager

FROM Employee E , Employee M

WHERE E.managerid = M.Empid

AND Dept='scott';

here your are splitting a single table in to 2.

the field u must use is 'empid,empname,managerid,dept'

table should be like this:

empid empname managerid dept

1001 ram null scott

1002 mani 1001 scott

1003 raj 1001 scott

like this...... ok