title: android left join query with room
body: <p>I am trying to change my sqlite database with room library. I am little confuse with left join query.</p>
  
  <p>I have implemented it with sqlite, but don't know how can I achieve same withh room?</p>
  
  <p>Here is my table creation:</p>
  
  <p><strong>first table: Notification</strong></p>
  
  <pre><code>db.execSQL("CREATE TABLE IF NOT EXISTS $TABLE_NAME ($COLUMN_ID INTEGER PRIMARY KEY, $ICON TEXT, $TITLE INTEGER," +
                  " $DATE INTEGER, $TYPE INTEGER,$URL TEXT, $MESSAGE INTEGER, FOREIGN KEY($TITLE) REFERENCES ${TableNotificationsTrans.getTableName(this)}(id)," +
                  "FOREIGN KEY($MESSAGE) REFERENCES ${TableNotificationsTrans.getTableName(this)}(id))")
  </code></pre>
  
  <p><strong>second table: Notification_Trans</strong></p>
  
  <pre><code>db.execSQL("CREATE TABLE IF NOT EXISTS $TABLE_NAME ($COLUMN_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, $COLUMN_EN TEXT, $COLUMN_GU TEXT, $COLUMN_HI TEXT)")
  </code></pre>
  
  <p>What I am doing is I am storing notification in <strong>notification</strong> table but its name and description will be stored with specific language, in <strong>notification_trans</strong>.</p>
  
  <p><strong>Query to achieve</strong> </p>
  
  <pre><code>DatabaseHelper.database!!.rawQuery("SELECT A.$COLUMN_ID, A.$ICON, N.${language.toLowerCase()} $TITLE, A.$DATE, A.$TYPE, A.$URL, M.${language.toLowerCase()} $MESSAGE FROM $TABLE_NAME A LEFT JOIN NotificationsTrans N ON A.$TITLE = N.id LEFT JOIN NotificationsTrans M ON A.$MESSAGE = M.id ORDER BY $DATE DESC LIMIT $pageNum*10, 10", null)
  </code></pre>
  
  <p><strong>Question</strong></p>
  
  <pre><code>How can I achieve same with room?
  </code></pre>
  
  <p><strong>Edit</strong></p>
  
  <p>My application is multi-language application, where I am getting notification title with specific language, like Hindi or Gujarati. I am storing notification details in <strong>notification</strong> table, while title in <strong>notification_trans</strong>.</p>
  
  <p>NotificationTrans have column with id, english, hindi, gujarati.</p>
  
  <p>When user asked for gujarati, I am retriving notification title from notificationTrans's column gujarati.</p>
  
  <p>I am able do so, in sqlite.</p>
  
  <p>But now I want it with Room</p>