meenal meenal - 5 months ago 10
MySQL Question

using groupby and order by with inner join

I am trying to use inner both GROUP BY and ORDER BY command in a same query with INNER JOIN, its is not happening.

i have Employee table:

+------------+------+
| id | fname | lname| |
+------------+------+
| 11 | ABCD | XHME |
| 12 | CDEF | LMOP |
| 13 | MNOP | DDDD |
+---------+---------+


emp_details table

+----+--------+-----------+--------------+--------------+-------+
| id | emp_id | company | joining_date | Leaving_date | salary|
+---------+---------+--------------------+--------------|-------+
| 1 | 11 | Company 1 | 1999-01-03 | 2001-07-08 | 12000 |
| 2 | 11 | Company 2 | 2005-07-09 | 2007-01-31 | 16000 |
| 3 | 11 | Company 3 | 2002-04-07 | 2015-04-28 | 23000 |
| 4 | 12 | Companyxyz| 2000-10-12 | 2004-03-09 | 17000 |
| 5 | 12 | TestCom | 2010-10-10 | 2014-10-10 | 35000 |
+---+---------+-----------+--------------+--------------+-------+


I want to display like this

+------------+--------------+------------------+
| User Name | Last Company | Last Drawn Salary|
+------------+--------------+------------------+
| ABCD XMHE | Company 3 | 23000 |
| LDEF LMOP | TestCom | 35000 |
+------------+--------------+------------------+


My query is like this

SELECT employee.id AS eid, employee.employer_id, employee.fname, employee.lname, emp_details.id as emid, emp_details.emp_id, emp_details.company, emp_details.joining_date, emp_details.leaving_date, emp_details.last_drawn_salary
FROM employee
INNER JOIN emp_details ON employee.id = emp_details.emp_id
WHERE employee.pan='".$pan."'
GROUP BY emp_details.emp_id
ORDER BY emp_details.id DESC


I also tried using like this
max(emp_details.id) as emid
but is still shows the 1st inserted column only. whats the problem here?

Answer
final answer

   select CONCAT_WS(' ', e.fname, e.lnam) AS `User Name`,ed.company as last_company,esd.salary as max_salery from (select max(Leaving_date) as Leaving_date,emp_id from emp_details group by emp_id) eld INNER JOIN emp_details ed on ed.Leaving_date=eld.Leaving_date and ed.emp_id=eld.emp_id INNER JOIN (select max(salary) as salary,emp_id from emp_details group by emp_id) esd on ed.emp_id=esd.emp_id inner join Employee e on e.id = ed.emp_id;
    +-----------+--------------+------------+
    | User Name | last_company | max_salery |
    +-----------+--------------+------------+
    | ABCD XHME | Company 3    |      23000 |
    | CDEF LMOP | TestCom      |      35000 |
    +-----------+--------------+------------+
    2 rows in set (0.00 sec)


Step By Step Answer 


select max(Leaving_date),emp_id from emp_details group by emp_id;
+-------------------+--------+
| max(Leaving_date) | emp_id |
+-------------------+--------+
| 2015-04-28        |     11 |
| 2014-10-10        |     12 |
+-------------------+--------+
2 rows in set (0.00 sec)

select max(salary),emp_id from emp_details group by emp_id;
+-------------+--------+
| max(salary) | emp_id |
+-------------+--------+
|       23000 |     11 |
|       35000 |     12 |
+-------------+--------+
2 rows in set (0.00 sec)

select * from (select max(Leaving_date) as Leaving_date,emp_id from emp_details group by emp_id) eld INNER JOIN emp_details ed on ed.Leaving_date=eld.Leaving_date and ed.emp_id=eld.emp_id;
+--------------+--------+------+--------+------------+--------------+--------------+--------+
| Leaving_date | emp_id | id   | emp_id | company    | joining_date | Leaving_date | salary |
+--------------+--------+------+--------+------------+--------------+--------------+--------+
| 2015-04-28   |     11 |    3 |     11 | Company 3  | 2002-04-07   | 2015-04-28   |  23000 |
| 2014-10-10   |     12 |    5 |     12 | TestCom    | 2010-10-10   | 2014-10-10   |  35000 |
+--------------+--------+------+--------+------------+--------------+--------------+--------+
2 rows in set (0.00 sec)

select ed.emp_id,ed.company as last_company,esd.salary as max_salery from (select max(Leaving_date) as Leaving_date,emp_id from emp_details group by emp_id) eld INNER JOIN emp_details ed on ed.Leaving_date=eld.Leaving_date and ed.emp_id=eld.emp_id INNER JOIN (select max(salary) as salary,emp_id from emp_details group by emp_id) esd on ed.emp_id=esd.emp_id;
+--------+--------------+------------+
| emp_id | last_company | max_salery |
+--------+--------------+------------+
|     11 | Company 3    |      23000 |
|     12 | TestCom      |      35000 |
+--------+--------------+------------+
2 rows in set (0.00 sec)
Comments