title: MySQL query to get the max, min with GROUP BY and multiple table joins in separate rows
body: <p>Table structure </p>
  
  <p><strong>employee_salary</strong></p>
  
  <pre><code>salary_id  |    emp_id    |   salary
  </code></pre>
  
  <p><strong>employee tabe structure</strong></p>
  
  <pre><code>emp_id | first_name | last_name | gender | email | mobile | dept_id | is_active
  </code></pre>
  
  <p><strong>Department:</strong> </p>
  
  <pre><code>dept_id | dept_name | manager_name | is_active
  </code></pre>
  
  <p><strong>Question:</strong></p>
  
  <p>Display department wise employee who is getting highest and lowest salary amount?</p>
  
  <p>I am using query</p>
  
  <pre><code>SELECT   max(salary) salary,  dept_name, first_name, dept_id , 'MAX' Type
  FROM    ( 
          SELECT     a.salary, c.dept_name, b.first_name, b.dept_id, a.salary_id
          FROM       employee_salary a
          LEFT JOIN  employee b 
          ON         a.emp_id = b.emp_id
          LEFT JOIN  department c 
          ON c.dept_id = b.dept_id
          ) t
  GROUP BY dept_id
  
  UNION ALL
  
  SELECT   min(salary) salary,  dept_name, first_name, dept_id , 'MIN' Type
  FROM    ( 
          SELECT     a.salary, c.dept_name, b.first_name, b.dept_id, a.salary_id
          FROM       employee_salary a
          LEFT JOIN  employee b 
          ON         a.emp_id = b.emp_id
          LEFT JOIN  department c 
          ON c.dept_id = b.dept_id
          ) t
  GROUP BY dept_id
  
  ORDER BY dept_id 
  </code></pre>
  
  <p>The output I am getting is shown below . 
  <strong>I am not able to get the respective first name from employee table and rest all other fields are showing correct values,</strong> </p>
  
  <pre><code>salary  dept_name   first_name  dept_id Type
  
  30000   dept_1      Paul        1       MIN
  98000   dept_1      Paul        1       MAX
  51000   dept_2      Aron        2       MAX
  20000   dept_2      Aron        2       MIN
  40000   dept_3      Steve       3       MAX
  40000   dept_3      Steve       3       MIN
  64000   dept_4      Henry       4       MAX
  64000   dept_4      Henry       4       MIN
  </code></pre>