Mysql subquery problem

Question

Hi, I have 2 tables:

• loans (columns: id, user)
• payback (columns: id, installment, scheduleDate)

If a loan is to be paid in 12 installments, then there are 12 payback records with loan id.

I would like to find out what users have a scheduleDate on which the sum of all their paybacks is greater than 2000.

SELECT user 
FROM {loans} ka 
WHERE 
(
   SELECT MAX(inst) FROM 
   (
        SELECT SUM(installment) AS inst 
        FROM {payback} 
        WHERE id IN 
        (
            SELECT id 
            FROM {loans} 
            WHERE user = ka.user
        )  
   GROUP BY scheduleDate
   ) as t1 LIMIT 0,1
) > 2000

I'm getting the error: Unknown column 'ka.user' in 'where clause'

Answer 1

Does this work as requested? It assumes, that payback.id is a foreign key referring to loads.id.

SELECT
  user, scheduleDate, sumInstallments
FROM
(
    SELECT
      l.user, pb.scheduleDate, SUM(pb.installment) AS sumInstallments
    FROM
      payback AS pb
    JOIN
      loans AS l
    ON
      l.id = pb.id
    GROUP BY
      l.user, pb.scheduleDate
) AS tempId
WHERE
  sumInstallments > 2000