mysql - HQL with join of 3 tables and where condition -

we had report generating hql join between 2 tables worked correctly. changes db design, need same thing 3 tables now. not getting desired results.

earlier, users had single role in system , role part of user table. in case following hql worked

<![cdata[select new com.test.reportsmodel.agentreport(u,              count( t.transactionid ), sum(case              when  t.status =  'created' 1              when  t.status =  'paid' 1              when  t.status =  'delivered' 1              else 0 end ))              user u             left join u.transactionsforagent t               (t.invoicedate between :startdate , :enddate)             u.role = 'externalagent'             group u.userid]]>     

now db design has changed allows users have multiple roles in system. hence useraccess table has been created has userid , role in it. take care of change above hql has been updated

 <![cdata[select new com.test.reportsmodel.agentreport(u,          count( t.transactionid ), sum(case          when t.status = 'created' 1          when t.status = 'paid' 1          when t.status = 'delivered' 1          else 0 end ))          user u         left join u.transactionsforagent t          (t.invoicedate between :startdate , :enddate)         left join u.useraccesses          (a.role = 'externalagent')         group u.userid]]> 

but query not giving correct result. there quite few hqls need modification because of change in role. wanted understand best way incorporate change in hql.