CODEWITHSUNDEEP
mysql
prudvi raju
prudvi raju

Reputation: 505

Query to fetch data from below table

MySQL Table information

emp_id   emp_name   manager_id
 1        PR          3
 2        ST          4
 3        OP          3 
 4        NP          5
 5        FGDG        12

Here manager_id again refers to the emp_id . I want to find the manager name and employee name based on the emp_id in a single Query statement.

Currently i am writing 2 Queries

  1. First fetching emp_name and manager_id(got emp_name) SELECT manager_id, emp_name FROM employee_manager WHERE emp_id = 2;
  2. Based on the manger_id in above query finding manager_name SELECT emp_name AS Manager_name FROM employee_manager WHERE emp_id = 1;

Is it possible to write it as a single Query instead of 2 Queries?

Upvotes: 0

Views: 50

Answers (1)

spencer7593
spencer7593

Reputation: 108370

Yes, it is possible to get the information back with a single query, on a single row. You can use a JOIN operation, and retrieve the matching row for the manager. For example:

SELECT e.emp_id
     , e.emp_name
     , e.manager_id
     , m.emp_id     AS manager_emp_id
     , m.emp_name   AS manager_emp_name
  FROM employee e
  LEFT
  JOIN employee m
    ON m.emd_id = e.manager_id
 WHERE e.emp_id = 4

Use an "outer join" operation, in case the manager_id column is NULL, or there isn't a row with an emp_id value equal to the value of the manager_id column. (In the example data, row with emp_id=5 has manager_id=12, but we don't see a row that has emp_id=12. The "outer join" allows the employee row to be returned, and return NULL values for the rows for the manager. With an "inner join", we wouldn't get any row back.

Upvotes: 2

Related Questions