CODEWITHSUNDEEP
mysqlsql
Vijay Rangan
Vijay Rangan

Reputation: 334

Subquery on Join - Where statement

I've been stuck on this issue for a while now. The problem is the following :

I have 3 tables. A students table, a payments table and a pivot course_student table. A student can take many courses and each student can make many payments for each course they take. This data is stored on my payments table with a reference to course_student_id.

I just need the last payment made by the student which holds the latest information about the student's payment.

Here's my query :

SELECT * 
FROM payments p
JOIN course_student cs ON cs.id = p.course_student_id
JOIN students s ON s.id = cs.student_id
GROUP BY p.course_student_id
HAVING max(p.id) IN ( SELECT id FROM payments )

The problem is that I'm getting back only the first record of each course_student from the payments table when actually I expect the last row.

Here's an example of what I want returned :

Students
student_name student_id course_student_id
XYZ                   1                 1

On the payments table, say I have 3 entries for course_student_id = 1

Payments
 id course_student_id amount_paid
 1                  1         100
 2                  1         250
 3                  1         500

I need the query to return all of the details of the last payment.

Upvotes: 0

Views: 57

Answers (3)

meshyx
meshyx

Reputation: 26

consider trying this solution. The subselect creates a new list of all the students payments for each course_student_id, and selects out the largest id.

SELECT * 
FROM payments p
JOIN course_student cs ON cs.id = p.course_student_id
JOIN students s ON s.id = cs.student_id
WHERE p.id in (
    SELECT MAX(id)
    FROM payments p2
    WHERE p2.course_student_id = p.course_student_id)

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use a derived table to get the max date for a student's payment. If the max date should be by course, include it in group by.

SELECT s.id, t.course_id, t.mxdate --other columns as needed
FROM course_student cs
JOIN students s ON s.id = cs.student_id
JOIN (select course_student_id, course_id, max(payment_date) as mxdate from payments
                                 ---^ --        ^ --change these columns to be appropriate
      group by course_student_id, course_id) t
on t.course_student_id = s.id

Upvotes: 3

zedfoxus
zedfoxus

Reputation: 37059

How about simplifying it and using limit and order by to get the latest payment information?

SELECT * 
FROM payments p
JOIN course_student cs ON cs.id = p.course_student_id
JOIN students s ON s.id = cs.student_id
ORDER BY p.id desc
LIMIT 1;

Upvotes: 0

Related Questions