Reputation: 2422
I've got four tables. The structure of these tables is shown below (I am only showing the relevant column names).
User (user_id)
User_RecordType (user_id, recordType_id)
RecordType (recordType_id)
Record (recordType_id, record_timestamp, record_value)
I need to find the most recent record_value
for each RecordType
that a given user has access to. Timestamps are stored as seconds since the epoch.
I can get the RecordTypes that the user has access to with the query:
SELECT recordType_id
FROM User, User_RecordType, RecordType
WHERE User.user_id=User_RecordType.user_id
AND User_RecordType.recordType_id=RecordType.recordType_id;
What this query doesn't do is also fetch the most recent Record for each RecordType that the user has access to. Ideally, I'd like to do this all in a single query and without using any stored procedures.
So, can somebody please lend me some of their SQL-fu? Thanks!
Upvotes: 2
Views: 849
Reputation: 52382
SELECT
Record.recordType_id,
Record.record_value
FROM
Record
INNER JOIN
(
SELECT
recordType_id,
MAX(record_timestamp) AS `record_timestamp`
FROM
Record
GROUP BY
recordType_id
) max_values
ON
max_values.recordType_id = Record.recordType_id
AND
max_values.record_timestamp = Record.record_timestamp
INNER JOIN
User_RecordType
ON
UserRecordType.recordType_id = RecordType.recordType_id
WHERE
User_RecordType.user_id = ?
Upvotes: 3