CODEWITHSUNDEEP
mysql
broinjc
broinjc

Reputation: 2699

same table join

Table employee has ssn and superssn coloumns. I want to show the supervisor of the supervisor of each employee (or null if none exists). So far I only have the supervisors of each employee:

SELECT p.fname AS sup,
group_concat(c.fname) AS emp 
FROM employee AS p
LEFT JOIN employee AS c 
ON (p.ssn = c.superssn) 
GROUP BY p.ssn;

Should I do another JOIN? Or something in GROUP BY?

Upvotes: 1

Views: 70

Answers (1)

Taryn
Taryn

Reputation: 247810

If you want the manager, employee and then the manager's supervisor, then you can join on the table employee multiple times:

SELECT p.fname AS sup,   -- Manager
    group_concat(c.fname) AS emp,  -- Employees
    sp.fname as SuperSuper   -- Manager Supervisor
FROM employee AS p
LEFT JOIN employee AS c 
    ON p.ssn = c.superssn
LEFT JOIN employee sp
    on p.superssn = sp.ssn
GROUP BY p.ssn;

Upvotes: 1

Related Questions