Hi,

I have two tables (drug_master, cds_drugstore) with cds_master.drug_id reference to drug_master.id.

I want to fetch all the drugids, drug_name, sum of avaialble drugs (if avaialable, or else null).

I have this native MySql query, which is working fine. but I am unable to port this in to HQL.

        select dm.id, dm.drug_name, sum(cds.qty_available)
        from drug_master dm
        left join cds_drugstore cds
            on dm.id = cds.drug_id
            and cds.cds_id = 1
            and cds.source_id = 'sourceName'
            and cds.source_type = 'c'
        group by dm.id

I've didn't see any examples using 'ON' keyword.  Because here I've to join tables based on 3 tables.

Some one please help. I need this very urgently.

Thanks in advance.


Regards,
ManiKanta