Welcome to WebmasterWorld Guest from 54.144.243.34

Forum Moderators: open

MySQL query - group by needed or something else?

selecting from two tables

   
8:24 am on Oct 5, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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?
3:24 pm on Oct 5, 2010 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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"
8:08 pm on Oct 6, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I'd expect you need to group by the year as well as employeeid.
3:14 pm on Oct 8, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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]

5:55 pm on Oct 8, 2010 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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.
10:52 am on Oct 22, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



thanks for all your help guys :)
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month