how to join 2 table on Hibernate in grails?

Question

i need help. i want to ask about Hibernate HQL on grails. i want to join 2 tables into 1 result.

My Controller

TRANSACTION.executeQuery("from TRANSACTION join SUBSCRIBER on TRANSACTION.subscriberID = SUBSCRIBER.msisdn where TRANSACTION.date = '2020-09-04'"

result: unexpected token: on

when i change my code same like this source This link

TRANSACTION .executeQuery("from TRANSACTION t1,SUBSCRIBER t2 where t1.subscriberID = t2.msisdn and TRANSACTION.date = '2020-09-04'")

result : Unknown column 'subscriber0_.pos' in 'field list'

My DB Mapping

class Transaction implements Validateable{
String id
String subscriberID
String currentBalance 
String date

static mapping = {
    datasource 'trx'
    table 'TRANSACTION'
    id generator: 'assigned', column: "ID"
    subscriberID column: "subscriberID"  <-- this value is equals to msisdn at Subscriber
    currentBalance column: "currentBalance"
    date column: "date" 
    version false
}

---

class Subscriber implements Validateable{

String id   
String msisdn
String firstName

static mapping = {
    datasource 'subs'
    table 'SUBSCRIBER'
    
    id column:"subscriberID"        
    msisdn column:"msisdn"      
    firstName column:"firstName"

    version false
    
}

i want to join them like this

| firstName | subscriberID | currentBalance | Date |

Answer 1

i solved it!

def query = "select table1.subscriberID,table2.msisdn from table1 t1, table2 t2 where t1.id = t2.id" query= table1.executeQuery(query);