MySQL query - group by needed or something else?

selecting from two tables

[HelenDev]

I have a query like this but it's not returning the result I want:

$mods_query = 'SELECT 
employees.id, 
employees.name, 
employees.grade 
MAX(employee_reviews.year) 
FROM employees 
JOIN employee_reviews ON employees.id=employee_reviews.employee_id 
WHERE employees.current=1 
GROUP BY employee_reviews.employee_id';

The tables are like this

employees

id | name | grade
-----------------
1 | jim | a
2 | bob | b
3 | joe | c

employee reviews

id | employee_id | year
-----------------------
1 | 2   | 2003
2 | 2   | 2008
3 | 1   | 2007
4 | 3   | 2006

and I want to select the employee information along with only the year of their most recent review, but the results I'm getting for the year are seemingly random. What am I doing wrong?

[enigma1]

try order and limit

$mods_query = "select ab.id, ab.name, cd.year from employees ab, employee_reviews cd where ab.id=cd.employee_id and ab.current=1 order by cd.year desc limit 1"

[deejay]

I'd expect you need to group by the year as well as employeeid.

[HelenDev]

Thanks for the replies guys, I think I have been able to find the solution.

Enigma, wouldn't that only give me one result rather than the whole staff list?

The penny has finally dropped that I needed to do this the other way around - select all the rows from employee_reviews rather than employees...

$mods_query = 'SELECT
employees.id,
employees.name,
employees.grade
MAX(employee_reviews.year) AS latest_review
FROM employee_reviews
RIGHT JOIN employees ON employees.id=employee_reviews.employee_id
WHERE employees.current=1
GROUP BY employee_reviews.employee_id';

[edited by: engine at 4:12 pm (utc) on Oct 8, 2010]

[enigma1]

Yes you mentioned one, is what I understood,

I want to select the employee information along with only the year of their most recent review

Otherwise remove the limit 1 to get all the employee reviews sorted by year.

[HelenDev]

thanks for all your help guys :)