CODEWITHSUNDEEP
androidsqlite
VansFannel
VansFannel

Reputation: 45921

Execute select with two tables on SQLiteDatabase

I'm developing an Android 3.1 application.

I have the following tables:

FormBlock
---------
FormId  (INTEGER) FOREIGN KEY TO Form.id
BlockId (INTEGER) FOREIGN KEY TO Block.id

Block
----------
id          (INTEGER) PRIMARY KEY
name        (TEXT)    NOT NULL
orderInForm (INTEGER) NOT NULL

And I want to do the following select:

SELECT block.blockId, block.name, block.orderInForm FROM Block, FormBlock WHERE FormBlock.FormId = 1 AND block.blockId = FormBlock.blockId;

How can I do that select statement with SQLiteDatabase.query(...) or rawQuery()?

Upvotes: 0

Views: 723

Answers (2)

zapl
zapl

Reputation: 63955

SELECT 
    block.blockId, block.name, block.orderInForm
FROM
    Block, FormBlock 
WHERE
    FormBlock.FormId = 1 AND block.blockId = FormBlock.blockId;

as query is

String table = "Block, FormBlock";
String[] columns = new String[] { "block.blockId", "block.name", "block.orderInForm" };
String where = "FormBlock.FormId = 1 AND block.blockId = FormBlock.blockId";
Cursor result = db.query(table, columns, where, null, null, null, null);

if the 1 is dynamic then 

int formId = 1;
String table = "Block, FormBlock";
String[] columns = new String[] { "block.blockId", "block.name", "block.orderInForm" };
String where = "FormBlock.FormId = ? AND block.blockId = FormBlock.blockId";
String[] whereArgs = new String[] { String.valueOf(formId) };
Cursor result = db.query(table, columns, where, whereArgs, null, null, null);

Upvotes: 2

waqaslam
waqaslam

Reputation: 68177

Try this:

String query = "SELECT * FROM Block WHERE id IN
                  (SELECT BlockId FROM FormBlock WHERE FormId = 1)";

Cursor c = database.rawQuery(query, null);

Upvotes: 1

Related Questions