bc120201817 Zohaib Siddiq bc120201817 Zohaib Siddiq - 7 months ago 12
SQL Question

Why my Select statement does not return any data?

I've created two tables with a foreign key. The insertion of data has succeeded, but when I use a SELECT statement then no data is showing. The code is below.

public static final String CREATE_TABLE_BLOCK_LIST = "CREATE TABLE " + TABLE_BLOCK_LIST
+ "(" + ID_BLOCK_LIST + " INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT 0,"
+ ORIGINATING_ADDRESS + " TEXT)";

public static final String CREATE_TABLE_BLOCKED_SMS = "CREATE TABLE " + TABLE_BLOCKED_SMS
+ "(" + ID_BLOCKED_SMS + " INTEGER PRIMARY KEY AUTOINCREMENT DEFAULT 0, "
+ MESSAGE_BODY + " TEXT,"
+ FK_ID_BLOCK_LIST + " INTEGER," +
"FOREIGN KEY(" + FK_ID_BLOCK_LIST + ") REFERENCES " + TABLE_BLOCK_LIST + "(" + ID_BLOCK_LIST +"))";


SELECT statement

SQLiteDatabase db = this.getWritableDatabase();
//cursor = db.rawQuery("SELECT * FROM " + TABLE_BLOCKED_SMS, null);
cursor = db.rawQuery("SELECT * FROM table_block_list " +
"JOIN table_blocked_sms " +
"ON table_block_list.id_block_list = table_blocked_sms.id_blocked_sms" , null);


Kindly guide me where the problem is.

Answer

Your join is implicitly INNER JOIN.

If your first table has data and your second one does not, an INNER JOIN will return no data.

What you need to do is use LEFT OUTER JOIN

SELECT * FROM table_block_list
    LEFT OUTER JOIN table_blocked_sms
        ON table_blocked_sms.id_blocked_sms = table_block_list.id_block_list