MySql Left outer Join does not work as expected

I've the following table structure.

Relationship between loan and loan_repayment is one-to-many

loan

---------------------------------------------------------------------------------------
   id   |                 loan_amount         |           loan_duration
---------------------------------------------------------------------------------------
    1   |                    60000            |                 3
    2   |                    80000            |                 4
---------------------------------------------------------------------------------------

loan_repayment

---------------------------------------------------------------------------------------
   id   |      loan_id     |      amount      |       month      |       status
---------------------------------------------------------------------------------------
    1   |          1       |       20000      |         1        |       Pending
    2   |          1       |       20000      |         2        |       Pending
    3   |          1       |       20000      |         3        |       Pending

    4   |          2       |       20000      |         1        |       Pending
    5   |          2       |       20000      |         2        |       Pending
    6   |          2       |       20000      |         3        |       Pending
    7   |          2       |       20000      |         4        |       Pending

I would like to fetch only the loan amount and its respective paid months count for each loan from the entire two tables. I'm doing a query like this.

SELECT loan.`loan_amount`, COUNT(loan_repayment.`loan_id`) FROM loan
LEFT OUTER JOIN loan_repayment ON
      (loan.`id`=loan_repayment.`loan_id` AND loan_repayment.`status`='Paid' )
GROUP BY loan_repayment.`loan_id`

I'm using left outer join here so that even if the loan_repayment table is null according to the where clause, I can still get the result as 0. But the problem is, it's only returning the following data. I don't know why is it now returning the following (loan) rows after that.

---------------------------------------------------------------------------------------
             loan_amount                  |        count(loan_repayment.loan_id)
---------------------------------------------------------------------------------------
                60000                     |                     0
---------------------------------------------------------------------------------------

Actully I've created a Hibernate criteria which includes many other restriction, but upon further investigating the generated sql, it narrow down to this point. Why does 2nd row data and its child data are not included in the result of the proposed sql?

Also it would be helpful if it can be replicated in hibernate criteria (Not a big deal, I can figure it out, but worried about the result of that sql).

Update :

If I changed the query from

loan_repayment.`status`='Paid'

to

loan_repayment.`status`='Pending'

I get the following result

---------------------------------------------------------------------------------------
             loan_amount                  |        count(loan_repayment.loan_id)
---------------------------------------------------------------------------------------
                60000                     |                     3
                80000                     |                     4
---------------------------------------------------------------------------------------

It's kind of strange on why it's not producing the same result with count as 0 for the exact opposite condition.

---

Just faced to this question while looking at previous questions with no accepted or upvoted answer.

Your query is correct except than the group by clause, you have grouped by a field of the table from right side of the left join (GROUP BY loan_repayment.loan_id), that's why you don't have the other values, you should grouped them by loan.id, let test it:

Schema:

create table loan( id int, loan_amount  int, loan_duration int);
insert into loan values
(1,60000,3),(2,80000,4);
create table loan_repayment (id int,loan_id int,amount int,month int,status varchar(20));
insert into loan_repayment values
(1 ,1 ,20000,1,'Pending'),(2 ,1 ,20000,2,'Pending'),(3 ,1 ,20000,3,'Pending'),
(4 ,2 ,20000,1,'Pending'),(5 ,2 ,20000,2,'Pending'),(6 ,2 ,20000,3,'Pending'),
(7 ,2 ,20000,4,'Pending');

now with this query:

SELECT l.loan_amount, COUNT(lr.loan_id) as Paied
FROM loan l
LEFT OUTER JOIN loan_repayment lr ON
      (l.id=lr.loan_id AND lr.status='paid')
GROUP BY l.id

you will get:

loan_amount Paied
60000       0
80000       0

you can also have both paid and pending together:

SELECT l.loan_amount, SUM(CASE
                            WHEN lr.status='paid' THEN 1
                            ELSE 0
                          END) as Paied,
                       SUM(CASE
                            WHEN lr.status='Pending' THEN 1
                            ELSE 0
                          END) as Pending

FROM loan l
LEFT OUTER JOIN loan_repayment lr ON
      (l.id=lr.loan_id)
GROUP BY l.id

Result:

loan_amount Paied   Pending
60000       0       3
80000       0       4

You can achieve above results with Hibernate HQL or Criteria api, I don't think if it be too much difficult to map the above queries(the first query which is what you tried) to Hibernate HQL or Criteria.