homepage Welcome to WebmasterWorld Guest from 54.242.126.9
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MySQL query - group by needed or something else?
selecting from two tables
HelenDev

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4211460 posted 8:24 am on Oct 5, 2010 (gmt 0)

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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4211460 posted 3:24 pm on Oct 5, 2010 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4211460 posted 8:08 pm on Oct 6, 2010 (gmt 0)

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

HelenDev

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4211460 posted 3:14 pm on Oct 8, 2010 (gmt 0)

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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4211460 posted 5:55 pm on Oct 8, 2010 (gmt 0)

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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4211460 posted 10:52 am on Oct 22, 2010 (gmt 0)

thanks for all your help guys :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved