Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

In Android SQLite db how to match particular columns values which have same id's in multiple tables of a database?

Tags:

android

sqlite

I had dumped data into SQLite from Json data that I am getting. Now my question is I have a single database with multiple tables I want to relate multiple tables using there id's.These are the 3 tables I had created.

This is first table where we have product list,

private static final String DATABASE_CREATE_PRODUCT =
            "CREATE TABLE if not exists " + "product_template" + " (" +
                    "_id" + " integer PRIMARY KEY autoincrement," +
                    "name" + "," +
                    "list_price" + "," +
                    "create_date" + "," +
                    "default_code" + "," +
                    "ean13" + "," +
                    "image_new" + "," +
                    "image_variant" +
                    ");";

This is second table where we have partner list,

    private static final String DATABASE_CREATE_PARTNER =
            "CREATE TABLE if not exists " + "res_partner" + " (" +
                    "_id" + " integer PRIMARY KEY autoincrement," +
                    "phone" + "," +
                    "mobile" + "," +
                    "name" +
                    ");";

This is third table where we have account line,

    private static final String DATABASE_CREATE_LINE =
            "CREATE TABLE if not exists " + "account_invoice_line" + " (" +
                    "product_id" + "," +
                    "partner_id" +
                    ");";

In third table I have two columns product_id and partner_id, I should match values of partner_id with second table column _id and I should get values of name column in it and through values in column partner_id I should match with first table _id and I should get values in name column.

Can any one help in creating relation between this tables?

like image 675
lakshman kumar Avatar asked Jul 04 '16 13:07

lakshman kumar


People also ask

How do I query two tables in SQLite?

To query data from multiple tables, you use INNER JOIN clause. The INNER JOIN clause combines columns from correlated tables. Suppose you have two tables: A and B. A has a1, a2, and f columns.

Can SQLite handle multiple users?

Yes SQLite can support multiple users at once. It does however lock the whole database when writing, so if you have lots of concurrent writes it is not the database you want (usually the time the database is locked is a few milliseconds - so for most uses this does not matter).

How can I compare two SQLite databases?

Basically - in order to compare two SQLite database files, you need to click the "Compare..." button. This will open up the "Comparison Details" dialog in which you'll fill in the paths to both SQLite database files and choose the comparison mode: Compare schema only- For comparing only SQL schema differences.


2 Answers

If you are trying to get the data, you are probably looking for SQL JOINS.

enter image description here

like image 154
theyouishere Avatar answered Sep 23 '22 08:09

theyouishere


Try something like this:

SELECT t1.name prod_name, t2.name part_name from product_template t1, res_partner t2, account_invoice_line t3 where t3.product_id = t1.id and t3.partner_id = t2.id
like image 20
Memme Avatar answered Sep 25 '22 08:09

Memme