CODEWITHSUNDEEP
mysqlsqljoin
Robert Dickey
Robert Dickey

Reputation: 834

MySQL Multiple Join Query with Limit on One Join

I have a MYSQL query I'm working on that pulls data from multiple joins. 

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

I have a unique situation where I would like one of the joins (the reports join) to be limited to the latest result only for that table (order by reports.timestamp desc limit 1 is what I use), while not limiting the result quantities for the overall query.

By running the above query I get the data I would expect, but only one record when it should return several.

My question:

How can I modify this query to ensure that I receive all possible records available, while ensuring that only the latest record from the reports join used? I expect that each record will possibly contain different data from the other joins, but all records returned by this query will share the same report record

Upvotes: 3

Views: 2780

Answers (2)

xQbert
xQbert

Reputation: 35323

Provided I understand the issue; one could add a join to a set of data (aliased Z below) that has the max timestamp for each student; thereby limiting to one report record (most recent) for each student.

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

Upvotes: 4

ScaisEdge
ScaisEdge

Reputation: 133360

for get all the records you should avoid limit 1 at the end of the query
for join anly one row from reports table you could use subquery as 

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

Upvotes: 1

Related Questions