Member Avatar for palavi

hi,
i have one table called EMP with 3 fields SSN,ENAME,MGRSSN.
query to retrieve all employees name and their respective manager names(only using this table no other table and ssn primary key.

i know till here..

SELECT ENAME,???
FROM EMP
WHERE SSN=MGRSSN ;

here im only getting manager's name..so how to get emp names and der manager's name...plz help this question is making me irritated bcoz im unable to answer..i think we need to use nested query dat is passing values of mgrssn to the where clause then select query in where clause..i dont know im guessing.

  • 1 Contributor
  • 1 Reply
  • 98 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by palavi
Member Avatar for palavi

Hi,
I got the answer for this,i want to share this answer with forum members since i might be useful for others.
we can solve this question using self join.the answer is

SELECT E.EMP,M.EMP
FROM EMP E,EMP M
WHERE  M.MGRSSN=E.EMPID;

dats it! here you are creating virtual table EMP M and comparing manager id with empid,so in result two columns created first is EMPNAME and 2nd MGRNAME.

if you give condition like this

SELECT E.EMP,M.EMP
FROM EMP E,EMP M
WHERE  E.EMPID=M.MGRSSN;

this is wrong.here wont get valid result.
any corrections will be appreciated.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.