Q:

How to do recursive queries on two tables using web sql database?

I'm in a html5 project using web sql database.
I have 2 tables.
Product table

id
name

Price table

id
product_id
date
price

A product can have multiple prices with different dates.
I tried something like it:
bd.x.transaction(function (tx) {
    tx.executeSql('SELECT * FROM Products WHERE id = ?', intProductId,
        function (tx, rs) {
            if (rs.rows.length > 0) {
                var objProduct = new clsProduct();
                objProduct.setId(rs.rows.item(0).id);
                objProduct.setName(rs.rows.item(0).name);

                // Query Price Start
                bd.x.transaction(function (tx) {
                    tx.executeSql('SELECT * FROM Prices WHERE product_id = ?', intProductId,
                        function (tx, rs) {
                            var x = rs.rows.length;
                            if (x > 0) {
                                var arrResult = [];
                                for (var i = 0; i < x; i++) {
                                    arrResult[i] = new clsPrice();
                                    arrResult[i].setId(rs.rows.item(i).id);
                                    arrResult[i].setDate(rs.rows.item(i).date);
                                    arrResult[i].setPrice(rs.rows.item(i).price);
                                }

                                // set prices
                                objProduct.setPrice(arrResult);
                            }
                        },
                        bd.onError
                    );
                });
                // Query Price End
            }
        },
        bd.onError
    );
});

The problem is that the Product query finish before Price query finish and objProduct.getPrice() return undefined.
If I put a alert in the Price query, records are found and I can see the result.
How can I do it?
Thank you.

A:

You want to use a compound query, where the SQL is something like:
SELECT * FROM Products WHERE id IN (SELECT * FROM Prices WHERE product_id = ?)

That way you just have one query executing; it's better from a SQL point of view.

