Need Help with Join Query

[richard457]

I am trying to do a simple Join of two table how Can I do that.

 Future<void> observe({@required String key}) async {
    final Document data1 = _databaseService.insert(
        id: '12',
        data: {'id': '12', 'name': 'richizo', 'docId': '13', 'table': 'a'});
    final Document data2 = _databaseService.insert(
        id: '13',
        data: {'id': '13', 'name': 'richie', 'docId': '13', 'table': 'b'});
    final q = Query(_databaseService.db,
        'SELECT name FROM table=a JOIN table=b ON a.docId=b.docId WHERE a.docId=13 ');

    // q.parameters = {'VALUE': 'users'};

    q.addChangeListener((results) {
      while (results.next()) {
        // final rowAsArray = results.rowArray;
        final rowAsDict = results.rowDict['*'].asMap;
        // log.d(rowAsArray);
        log.d(rowAsDict.json);
        // print(rowAsArray[0]);
      }
      results.dispose();
    });
  }

[Rudiksz]

I'm not exactly sure what you are trying achieve based on your example documents (they seem circular), but JOINS are generally done like this:

SELECT b.name FROM a JOIN b ON a.docId=b.docId WHERE a.table = "a" AND a.docId = "13"

As there are no tables in couchbase lite and you select from the same database, the values in FROM and JOIN clauses are simply aliases.

In other words: SELECT * FROM table AS a WHERE a.docId = 13 is the same as SELECT * FROM a WHERE a.docId = 13 or SELECT * WHERE docId = 13

In SQL terms think of it as a JOIN on the same table, where the table being the whole database.

[richard457]

Thank you so much for helping me out on Query, this was a lifesaver to me as I couldn't get my head around the Couchbase Query, I really appreciate your help. @Rudiksz