I've got THREE MYSQL TABLES (innoDB) :
NAMES
id nid version fname lname birth
RELATIONS
id rid version idname idperson roleid
ROLES
id role
I want to select the last version of each RELATIONS joined to the last version of their related NAMES for a particular idperson (and the name of the ROLE)
Of course, idperson will have 0, 1 or more relations and there will be one or more versions of RELATIONS and NAMES
I wrote something like :
SELECT A.id,A.nid,MAX(A.version),A.idname,A.idperson,A.roleid,B.id,B.role
FROM RELATIONS A
INNER JOIN
ROLES
ON A.roleid = B.id
INNER JOIN
(SELECT id,nid,MAX(version),fname,lname,birth FROM NAMES) C
ON A.idname = C.id
WHERE A.idperson = xx
It doesn't work maybe because MAX() seems to return only one line...
How to get the maximum value for more than one line in this joining context?
PS: how do you generate this kind of nice data set?
i.e. :
id home datetime player resource
---|-----|------------|--------|---------
1 | 10 | 04/03/2009 | john | 399
2 | 11 | 04/03/2009 | juliet | 244
5 | 12 | 04/03/2009 | borat | 555
8 | 13 | 01/01/2009 | borat | 700
Adding a GROUP BY statement, both in the subquery you have, as well as in the outer query should allow the MAX function to generate the result that you're looking for.
This is untested code, but should give you the result that you're looking for:
SELECT A.id,A.nid,MAX(A.version),A.idname,A.idperson,A.roleid,B.id,B.role
FROM RELATIONS A
INNER JOIN
ROLES
ON A.roleid = B.id
INNER JOIN
(SELECT id,nid,MAX(version),fname,lname,birth FROM NAMES GROUP BY fname,lname) C
ON A.idname = C.id
WHERE A.idperson = xx
GROUP BY fname,lname
Alternatively, if it works better for your database architecture, you can use any unique identifier for the employees you'd like (possibly nid?).
As to the question that you've posed in your PS, I'm unsure as to what you're asking. I don't seem a home, datetime, player, or resource field in the examples of your tables that you've provided. If you could clarify, I'd be happy to try and help you with that as well.