3

I want to join two tables namely staff and staff exam time tables. A staff can attend N number of Exams. My result should be staff id, staff name, end time of the last completed exam.

My Table structure is below

staff table

  staff_id  staff_full_name     staff_status

    500     Sakthi          active

    550     Siraj           active

    600     Shihab K H      active

    620     John David      active

    670     Javed Akthar        active

examtime table 

 examtime_id    examtime_staffid    examtime_endtime

    100     500         2014-10-10

    101     600         2016-05-01

    102     670         2016-06-10

    103     670         2014-04-01

    104     670         2016-06-13

    105     670         2016-06-11

SQL QUERY FOR THE RESULT SET IS BELOW

SELECT S.staff_id, S.staff_full_name, ET.examtime_endtime
FROM staffs S LEFT JOIN examtime ET ON ET.examtime_staffid = S.staff_id
WHERE 1 AND S.staff_status =  'active' GROUP BY S.staff_full_name ORDER BY S.staff_full_name ASC , ET.examtime_endtime DESC

But I am getting the result set as below. It is fetching the first record of the exam time table irrespective of the exam end time. See below Result set ( Javed Akthar Latest exam date is 2016-06-13 but it is fetching 2016-06-10 ).

500 Sakthi      2014-10-10

600 Shihab KH   2016-05-01

670 Javed Akthar    2016-06-10
Tassadaque
  • 8,129
  • 13
  • 57
  • 89
Preethy
  • 722
  • 2
  • 10
  • 27
  • 2
    Your `GROUP BY` is causing the issue, because you want it to mean "read my mind" when in fact it [doesn't do that](https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html): "... a MySQL extension to the standard SQL ... In this case, the server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate, which is probably not what you want." – Damien_The_Unbeliever Jun 13 '16 at 07:46

2 Answers2

2

The problem is with your GROUP BY clause, you didn't specify which date you want for each one, so it is randomly selecting one, not necessarly the maximum one.

You can find a good explanation about this behaviour here in @mjv answer

Try this query:

SELECT S.staff_id, S.staff_full_name, max(ET.examtime_endtime) as max_endTime
FROM staffs S 
LEFT JOIN examtime ET
 ON (ET.examtime_staffid = S.staff_id)
WHERE S.staff_status =  'active' 
GROUP BY S.staff_id ,
         S.staff_full_name 
ORDER BY S.staff_full_name ASC ,
         max_endTime DESC

It will select the maximum date for each staff_id and will order by it. In general - it is common to specify all the columns that represent each group(in this case staff_id,staff_full_name) in the GROUP BY clause and all other columns with an aggregation function(AVG/MAX/MIN..) , this will help you avoid this kind of problems in the future.

Community
  • 1
  • 1
sagi
  • 40,026
  • 6
  • 59
  • 84
1

If you just want the most recent exam time for each staff and don't require any other columns from that table, you can do this with standard SQL and avoid the hazard you've introduced by using a MySQL "extension" (wherein you can have columns that aren't in aggregates and aren't in the GROUP BY and MySQL selects an arbitrary value for that column):

SELECT MIN(S.staff_id) as staff_id,
       S.staff_full_name,
       MAX(ET.examtime_endtime) as examtime_endtime
FROM staffs S LEFT JOIN examtime ET ON ET.examtime_staffid = S.staff_id
WHERE S.staff_status =  'active'
GROUP BY S.staff_full_name
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Actually, `staff_id` is unique , no need for `MIN()` on it. – sagi Jun 13 '16 at 07:56
  • @sagi - To be standard, it has to be in either the GROUP BY or in an aggregate. Since we know it's unique, it *doesn't* matter which one we use. I prefer to minimize the size of the `GROUP`ing keys, you prefer to eliminate an "unnecessary" aggregate. Which approach is correct can depend on a number of factors, and (since the above is using standard SQL) the trade-offs involved may differ on different platforms/versions. – Damien_The_Unbeliever Jun 13 '16 at 08:01