I am working on a student subscription database in MySQL using PhpMyAdmin. I have the following tables:

Students Table:
| student_id | student_name |

Plans Table:
| plan_id | plan_name |

Subscriptions Table:
| subscription_id | student_id | plan_id | amount_paid | payment_date |

I want to write a query that returns one row per student showing:

• Student name

• Their last 3 subscription plan names (ordered by payment_date descending)

• Corresponding amounts paid

For example, the output should look like this:

| student_name | plan_1 | amount_1 | plan_2 | amount_2 | plan_3 | amount_3 |

I tried the following query:

SELECT 
    s.student_name,
    p1.plan_name AS plan_1, sub1.amount_paid AS amount_1,
    p2.plan_name AS plan_2, sub2.amount_paid AS amount_2,
    p3.plan_name AS plan_3, sub3.amount_paid AS amount_3
FROM students s
LEFT JOIN subscriptions sub1 ON s.student_id = sub1.student_id
LEFT JOIN plans p1 ON sub1.plan_id = p1.plan_id
LEFT JOIN subscriptions sub2 ON s.student_id = sub2.student_id
LEFT JOIN plans p2 ON sub2.plan_id = p2.plan_id
LEFT JOIN subscriptions sub3 ON s.student_id = sub3.student_id
LEFT JOIN plans p3 ON sub3.plan_id = p3.plan_id
WHERE sub1.payment_date = (SELECT MAX(payment_date) FROM subscriptions WHERE student_id = s.student_id)
   OR sub2.payment_date = (SELECT MAX(payment_date) FROM subscriptions WHERE student_id = s.student_id)
   OR sub3.payment_date = (SELECT MAX(payment_date) FROM subscriptions WHERE student_id = s.student_id);

Problem:

1. The query duplicates joins and does not correctly pick the last 3 subscriptions.

2. The WHERE condition only selects the latest subscription, so plan_2 and plan_3 are often wrong or NULL.

3. Hardcoding plan_1, plan_2, plan_3 is not scalable.

Questions:

1. How can I write a query that correctly returns the last 3 subscription plans with amounts per student in a single row?

2. Is there a way to do this dynamically without hardcoding columns for each subscription?