CODEWITHSUNDEEP
mysqlsqlselectjoinself-join
Abhinav
Abhinav

Reputation: 8168

MYSQL- SELF REFERENCING FOREIGN KEY

I have a self-referencing foreign key (reportsTo) which refers to emp_id of the same table

I want to find out the employees who reports to their seniors.

I ran the following query

mysql> SELECT GROUP_CONCAT(emp_name SEPARATOR ',') as Employees,reportsTo from employees GROUP BY reportsTo;
+-----------------------------+-----------+
| Employees                   | reportsTo |
+-----------------------------+-----------+
| ram,ham,anir                |         1 |
| abhi,kuanl,rakesh,raja,rama |         2 |
| vicky,sam                   |         3 |
| dhanapal,naina              |         4 |
+-----------------------------+-----------+

BUT IN THE REPORTSTO COLUMN, I want the persons name whom they are reporting to.

Upvotes: 2

Views: 1061

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Use SELF JOIN to fetch the name of the reporting to employees.

Try this: 

SELECT GROUP_CONCAT(e.emp_name SEPARATOR ',') AS Employees, 
       e.reportsTo, e1.emp_name
FROM employees e 
INNER JOIN employees e1 ON e.reportsTo = e1.emp_id 
GROUP BY e.reportsTo;

Upvotes: 4

Related Questions