samedi 25 juin 2016

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?

Aucun commentaire:

Enregistrer un commentaire