How to use left join in grails?

I have the below query which is fetching the correct results in mysql when i write the same query in grails it is throwing an error "Path expected for join"

select u.username,u.transactioncode,count(distinct t.rolename) roles
from user u 
left join transaction t on u.transactioncode=t.transactioncode 
group by u.username, u.transactioncode; 

How to change the query?

Modifying the query to make it work in grails

function(int id)
 {
   def sql = new Sql(dataSource)
        def output = sql.rows("select 
        u.username,u.transactioncode,count(distinct t.rolename) roles from 
        user u left join transaction t on 
        u.transactioncode=t.transactioncode where u.userid=:id group by 
        u.username, u.transactioncode")
       sql.close();
 }

 It throws an error in the where condition You have an error in your SQL 
 syntax; check the manual that corresponds to your MySQL server version for 
 the right syntax to use near ':id.

Solution

I think the easiest way would be to use Groovy SQL. For this to work, you should inject dataSource into your service or controller:

import javax.sql.DataSource

DataSource dataSource

and then execute the query like

new Sql(dataSource).rows(q)

Where q would be your exact query.

Here is a good tutorial for using Groovy SQL with Grails.

I don't even know if it's possible to use left joins in inline HQL - and that's what should be used with executeQuery. Criterias, as mentioned by @JMa, would be a valid option, but they - I think - are generally used to query domain objects and you need custom results set. This is another good explanation for when to use different kinds of querying options in Grails.