CODEWITHSUNDEEP
javamysqlhibernate
Harpreet Singh
Harpreet Singh

Reputation: 1

Hibernate query not returning Zero value

Same query working in SQL

Select count(tbl_leads.lead_Status_Id) , tbl_LeadStatus.LeadStatus_Type FROM tbl_LeadStatus LEFT JOIN tbl_leads ON tbl_LeadStatus.LeadStatus_id = tbl_leads.lead_Status_Id GROUP BY tbl_LeadStatus.LeadStatus_Type;

Hibernate Query

Select s.LeadStatus_Type, count(l.status) FROM Status s "
                + "LEFT JOIN Lead l ON l.status = s.LeadStatus_id "
                + "GROUP BY s.LeadStatus_Type"

Expecting output is this 

   Count  LeadStatus_Type
    '0'   'Cancelled' 
    '0'   'In-Progress' 
    '1'   'New' 
    '0'   'Sold' 
    '0'   'UnAssigned'

And HQL return this

'1', 'New'

Upvotes: 0

Views: 545

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

Your join condition looks off. In HQL we join from an entity to the other entity which exists as a property of the first entity. Most importantly, there is no ON clause as that relationship is already known within Hibernate. Try the following:

SELECT s.LeadStatus_Type, COUNT(l.status)
FROM Status s
LEFT JOIN s.LeadStatus_id l
GROUP BY s.LeadStatus_Type

Upvotes: 1

Related Questions