SQLite - add new itens to result

Question

I'm using cordova SQLite storage plugin in my application Android. I need get result from 2 tables to display in view, my tables are "posts" and "metas"...

So I can publish 'products' (posts table) and informations like price and color are save in metas table. The problem is when I need show metas from products, I need list and after get information from table metas, but I need add metas in principal result (from first query).

Actually is returned

    results.rows.item(i).id
    results.rows.item(i).title
    results.rows.item(i).date

and I want add others items like this

    results.rows.item(i).id
    results.rows.item(i).title
    results.rows.item(i).date
    results.rows.item(i).price
    results.rows.item(i).color

But don't know how to do this, can you help me?

This is my functions...

function query( sql, callback ){

    db.transaction(function(transaction) {

        var executeQuery = sql;
        transaction.executeSql(executeQuery, [ ],
        function(tx, result) {
            //Success

            if( typeof( callback ) == 'function' ){
                callback( result );
            }
        },
        function(error){
            // Error
        });
    }
}


function get_meta( data, strMetaKey, callback ){

    for( i=0; i < data.length; i++ ){

        // QUERY
        query( /* SQL WHERE id = i.id AND meta_key = strMetaKey*/, function( result ){


            // here is my problem, I can't add 'price' to data
            data.rows.item( i ).price = result.rows.item(0).meta_value;
        });

    }

    if( typeof( callback ) == 'function' ){
        callback( data );
    }
}



new_data = '';


// getting products
query( /* my query */, function( result ){


    // getting price
    get_meta( result, 'price', function( result ){
        new_data = result;
    });

    // getting color
    get_meta( result, 'color', function( result ){
        new_data = result;
    });
});

Database schema

posts
ID
title
date
type


metas
id 
meta_key
meta_value

Answer 1

To add specific meta keys to a query on the main table, you can use correlated subqueries to look the values up:

SELECT ID,
       title,
       date,
       (SELECT meta_value
        FROM metas
        WHERE id       = posts.id
          AND meta_key = 'price'
       ) AS price,
       (SELECT meta_value
        FROM metas
        WHERE id       = posts.id
          AND meta_key = 'color'
       ) AS color
FROM posts;