Hibernate HqlL: Count and Group By including zero?

Question

I have 2 tables: Claim and Type_Claim. Claim has an external key on Type_Claim. On Hibernate the Bean that represents Claim table has TypeClaim as an attribute.

  Claim

 ID    TYPE
 1      2
 2      2
 3      4
 4      1

  Type_Claim

 ID    Description
 1     "Hello"
 2     "Hi"
 3     "House"
 4     "Welcome"
 5     "Bye"

Now i made this query:

SELECT tc.description, COUNT(*) 
FROM Claim claim"
LEFT OUTER JOIN claim.typeClaim tc  
GROUP BY tc.description ";

I want to obtain this:

Description  Count
  "Hello"     1
  "Hi"        2
  "House"     0
  "Welcome"   1
  "Bye"       0

But i obtain this:

Description  Count
  "Hello"     1
  "Hi"        2
  "Welcome"   1

How can i include the 0 results on the query? I tried with RIGHT JOIN but i got same result.

Answer 1

Try this:

SELECT tc.description, count(cl.type)
FROM type_claim tc
LEFT OUTER JOIN claim cl ON
cl.type = tc.id
GROUP BY tc.description

It worked for me: