MySQL多个连接查询限制在一个连接

Question

我有一个MYSQL查询我正在从多个连接拉数据。

select students.studentID, students.firstName, students.lastName, userAccounts.userID, userstudentrelationship.userID, userstudentrelationship.studentID, userAccounts.getTexts, reports.pupID, contacts.pfirstName, contacts.plastName, reports.timestamp 

from userstudentrelationship 

join userAccounts on (userstudentrelationship.userID = userAccounts.userID) 
join students on (userstudentrelationship.studentID = students.studentID) 
join reports on (students.studentID = reports.studentID) 
join contacts on (reports.pupID = contacts.pupID) 

where userstudentrelationship.studentID = "10000005" AND userAccounts.getTexts = 1 ORDER BY reports.timestamp DESC LIMIT 1 

我有一个独特的情况，我想的加入一个的报告（加入）仅限于最新的结果只对表（由reports.timestamp递减限制1级是我使用）同时不限制整个查询的结果数量。

通过运行上面的查询，我得到我期望的数据，但只有一条记录，当它应该返回几个。

我的问题：

如何修改这个查询，以确保我收到的所有可能的记录可用，同时确保仅从报告的最新记录连接使用？我希望每一个记录将可能包含不同的数据从另一个连接，但此查询返回的所有记录将共享相同的报告记录

Answer 1

提供我明白这个问题;可以将一个连接添加到一组数据（下面的别名Z），该连接对每个学生都有最大时间戳;从而限制每个学生的一个报告记录（最新）。

SELECT students.studentID 
    , students.firstName 
    , students.lastName 
    , userAccounts.userID 
    , userstudentrelationship.userID 
    , userstudentrelationship.studentID 
    , userAccounts.getTexts 
    , reports.pupID 
    , contacts.pfirstName 
    , contacts.plastName 
    , reports.timestamp 
FROM userstudentrelationship 
join userAccounts 
    on userstudentrelationship.userID = userAccounts.userID 
join students 
    on userstudentrelationship.studentID = students.studentID 
join reports 
    on students.studentID = reports.studentID 
join contacts 
    on reports.pupID = contacts.pupID 
join (SELECT max(timestamp) mts, studentID 
     FROM REPORTS 
     GROUP BY StudentID) Z 
    on reports.studentID = Z.studentID 
and reports.timestamp = Z.mts 
WHERE userstudentrelationship.studentID = "10000005" 
    AND userAccounts.getTexts = 1 
ORDER BY reports.timestamp 

Answer 2

为得到所有的记录，你应该避免限制1在查询结束
从报告表加入一行你可以使用子查询作为

select 
    students.studentID 
    , students.firstName 
    , students.lastName 
    , userAccounts.userID 
    , userstudentrelationship.userID 
    , userstudentrelationship.studentID 
    , userAccounts.getTexts 
    , t.pupID 
    , contacts.pfirstName 
    , contacts.plastName 
    , t.timestamp 

from userstudentrelationship 

join userAccounts on userstudentrelationship.userID = userAccounts.userID 
join students on userstudentrelationship.studentID = students.studentID 
join (
    select * from reports 
    order by reports.timestamp limit 1 
) t on students.studentID = t.studentID 
join contacts on reports.pupID = contacts.pupID 

where userstudentrelationship.studentID = "10000005" 
AND userAccounts.getTexts = 1