Help me with JOIN query with date and limits

[hmoner]

Hi guys
I've these tables:
- PERSON (id,name,age,....)
- EXAM (id,date,note,exam_type,id_user)

One person has 0 or more exams.

I have to do this report:

name, age, date, note, exam_type

This looks easy, but I have to list for each person, his/her LAST EXAM GIVEN. I mean, only the last exam must be shown for each person, and well, if person has no exam yet... to show blank (if possible)

Any way to do this with mySQL 4.x ? ( not 5 upgrade yet )
Thanks

 

[PHV]

One ANSI SQL way:
SELECT P.name,P.age,E.note,E.exam_type
FROM PERSON P LEFT JOIN (
SELECT X.id_user,X.note,X.exam_type FROM EXAM X INNER JOIN (
SELECT id_user,MAX(date) LastDate FROM EXAM GROUP BY id_user
) M ON X.id_user=M.id_user AND X.date=M.LastDate
) E ON P.id=E.id_user

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hmoner]

Hi PHV
Did I tell you that you're GOD ?

The query worked GREAT ! but well, I get a null when user has no exam yet, but I will be able to manage this.

Thanks A LOT again !!!

 

[r937]

another way --

SELECT P.name
     , P.age
     , X.note
     , X.exam_type
  FROM PERSON P 
LEFT OUTER
  JOIN EXAM X 
    ON X.id_user = P.id
   AND X.date =
       ( select MAX(date) 
           FROM EXAM 
          where id_user = P.id )

to me, this method is simpler, and it reads a lot more like the english equivalent

r937.com | rudy.ca

 

[hmoner]

Wow !
Thanks Rudy, the query worked perfect !
You're right, this is simpler, and it works in mySQL 4.1.x also... great !!!