Querying multiple tables using SqLite

Question

Hi, I have a question about SqLite.

I have a Table called "Alphabets" It cantains list of Alphabets:

id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, alphabet TEXT

Example 'Alphabets"table:

id | alphabet

1 | A

2 | B

3 | C

4 | D

5 | E

6 | F ...

This table gets populated by list of all Alphabets.

Then I have another table called Names.

id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, alphid INTEGER, name TEXT

"alphinteger" keeps reference to the Alphabets tables 'id'

Example Names table:

id | alphid | name

1 | 2 | Baseball

2 | 2 | Basketball

3 | 3 | Cricket Ball

4 | 6 | Football

5 | 6 | Fooshball

6 | H | Hockeyball

...

Now I am not sure how to query the database such that, for every Alphabet in "Alphabets" table the querry tries to find if the corresponding name exists.

I want the query to run even for the Alphabets for which there is no entry and return empty string for those items.

I tried this:

gatherData: function(){
  var Data = [];
  this.dbConn.transaction(function (transaction) {
     transaction.executeSql('SELECT * FROM `Alphabets`', [], function (transaction, results){
           var len = results.rows.length, i;
           for (i = 0; i < len; i++) {
              var alph = results.rows.item(i).alphabet
              Data.push(alph);
              var nameArr = [];
transaction.executeSql('SELECT * FROM `Names` WHERE `alphid`=?', [alph], function (transaction, res){
             var n = res.rows.length, j;
             if(glen == 0)
{
 nameArr.push([]);
}
              else
{
              for (j = 0; j < glen; j++) {
     var tempN = [];
                   tempN.push(res.rows.item(j).name);
                   nameArr.push(tempN);
              }
            }
            Data.push(nameArr);
          },classObj.errorHandler);
           }
     },classObj.errorHandler);
  });

At the end of this, I expect the Data array to look like this:

Data = [['A', []], ['B', ['Baseball', 'Basketball']], ['C', ['Cricketball']], ['D', []], .....];

and so on.

But this is not what I am getting.

Is it a problem to nest a transaction inside a transaction? How can I achieve what I am trying?

Thanks in advance.

Answer 1

Hi, Anyone got any solution to this?

Thanks a lot.