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
  • 116 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by palavi

Dani AI

Generated

Good catch, — a self‑join is the right idea. The table holds each employee row and a pointer (MGRSSN) to another row's SSN. To pair an employee with their manager you must join the table to itself so the employee side’s manager key is matched to the manager row’s SSN.

A clear, modern form uses explicit JOIN and aliases. Use LEFT JOIN when you want employees with no manager shown (top of the hierarchy), or INNER JOIN when you only want those who have managers.

SELECT e.ename AS Employee,
       COALESCE(m.ename, '(no manager)') AS Manager
FROM EMP AS e
LEFT JOIN EMP AS m
  ON e.mgrssn = m.ssn;

To get manager lists and direct-report counts, aggregate the same self‑join:

SELECT m.ename AS Manager, COUNT(e.ssn) AS DirectReports
FROM EMP AS m
LEFT JOIN EMP AS e
  ON e.mgrssn = m.ssn
GROUP BY m.ename
ORDER BY DirectReports DESC;

Practical tips: make SSN the primary key and index MGRSSN for join performance; add a foreign key constraint to keep referential integrity (so managers must exist) unless you intentionally allow NULL/broken references. Watch for self-references or cycles if you ever walk the management chain — in SQL Server use a recursive CTE for hierarchical queries. Finally, about the two WHERE variants you tried: the direction matters conceptually — the employee row’s MGRSSN must be matched to the manager row’s SSN; swapping those sides or the wrong columns will return incorrect pairs.

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.