Android Sqlite inner join doesn't work

Question

i'm trying to inner join between 2 tables on Sqlite

this is my code for the inner join:

public PicoEvent getEvent(String eventCode)
{
    //Get the readable database instance
    SQLiteDatabase db = getReadableDatabase();      
    //create string for user table inner join
    String userTable1 = UsersTable.TABLE_NAME + "1";
    String userTable2 = UsersTable.TABLE_NAME + "2";
    //create query for getting the event's details
    String sql1 = "Select * From " + EventsTable.TABLE_NAME
    //set the user tables once as users1 and once as users2 in the inner join process
    + " Inner Join " + UsersTable.TABLE_NAME + " As " + userTable1 + " On "
    + userTable1 + "." + UsersTable.USER_ID + " = " + EventsTable.TABLE_NAME + "." + EventsTable.SIGNED_IN_USER_ID
    + " Inner Join " + UsersTable.TABLE_NAME + " As " + userTable2 + " On "
    + userTable2 + "." + UsersTable.USER_ID + " = " + EventsTable.TABLE_NAME + "." + EventsTable.OWNER_USER_ID 
    + " Where " + EventsTable.TABLE_NAME + "." + EventsTable.EVENT_CODE + " = ?";               

    //selection arguments
    String[] selectionArgs = new String[] {eventCode}; 

    //Get the cursor for event's details query
    Cursor cursor = db.rawQuery(sql1, selectionArgs);
    Log.d("query", sql1.replace("?", eventCode));
    Log.d("cursor", String.valueOf(cursor.getCount()));
}

it creates this query:

Select * From Events 
Inner Join Users As Users1 On Users1.User_Id = Events.Signed_In_User_Id 
Inner Join Users As Users2 On Users2.User_Id = Events.Owner_User_Id 
Where Events.Event_Code = 10231009

the cursor always return 0 results...

when i'm dropping the inner join - i get the cursor with the event..

any ideas ?

Update --added create statement

/**
 * Get the query to create the Users table
 * @return the query for the creation of the table ready to be executed
 */
private String getCreateUsersTableQuery()
{
    //Create the create query string
    String createQuery = "Create Table If Not Exists " + UsersTable.TABLE_NAME + "("
            + UsersTable.USER_ID + " Varchar(50) Primary Key Not Null,"
            + UsersTable.NAME + " Varchar(50) Not Null,"
            + UsersTable.FIRST_NAME + " Varchar(50),"
            + UsersTable.PROFILE_PICTURE + " Blob"
            + ")";
    return createQuery; 
}

gives me the create query:

Create Table If Not Exists Users(
     User_Id Varchar(50) Primary Key Not Null,
     Name Varchar(50) Not Null,
     First_Name Varchar(50),
     Profile_Picture Blob)


/**
 * Get the query to create the Events table
 * @return the query for the creation of the table ready to be executed
 */
private String getCreateEventsTableQuery()
{
    //Create the create query string
    String createQuery = "Create Table If Not Exists " + EventsTable.TABLE_NAME + "("
            + EventsTable.EVENT_CODE + " Varchar(50)Not Null,"
            + EventsTable.SIGNED_IN_USER_ID + " Varchar(50)Not Null,"
            + EventsTable.NAME + " Varchar(50) Not Null,"
            + EventsTable.PLACE + " Varchar(100),"
            + EventsTable.START_TIME + " Integer Not Null,"
            + EventsTable.END_TIME + " Integer Not Null,"    
            + EventsTable.OWNER_USER_ID + " Varchar(50) Not Null,"
            + EventsTable.IS_SHARING_PICTURES + " Integer Default 1 Not Null,"
            + EventsTable.IS_ASK_BEFORE_SHARING + " Integer  Default 1 Not Null,"
            + "Primary Key(" + EventsTable.EVENT_CODE + "," + EventsTable.SIGNED_IN_USER_ID + "),"
            + "FOREIGN KEY(" + EventsTable.SIGNED_IN_USER_ID + ") REFERENCES " + UsersTable.TABLE_NAME + "(" + UsersTable.USER_ID + "),"
            + "FOREIGN KEY(" + EventsTable.OWNER_USER_ID + ") REFERENCES " + UsersTable.TABLE_NAME + "(" + UsersTable.USER_ID + ")"
            + ");";
    return createQuery;
}

gives me the query:

 Create Table If Not Exists Events(
     Event_Code Varchar(50)Not Null,
     Signed_In_User_Id Varchar(50)Not Null,
     Name Varchar(50) Not Null,Place Varchar(100),
     Start_Time Integer Not Null,
     End_Time Integer Not Null,
     Owner_User_Id Varchar(50) Not Null,
     Is_Sharing_Pictures Integer Default 1 Not Null,
     Is_Ask_Before_Share Integer  Default 1 Not Null,
     Primary Key(Event_Code,Signed_In_User_Id),
     FOREIGN KEY(Signed_In_User_Id) REFERENCES Users(User_Id),
     FOREIGN KEY(Owner_User_Id) REFERENCES Users(User_Id));

i'm trying to inner join between the Events Owner_User_Id and Signed_In_User_Id with their matching users from Users table according to their Id

Answer 1

I hope you can still use an answer. It turns out that using AS is not working. I am using a few inner-joins myself and they only work if I leave out the AS completely.

"Inner Join " + UsersTable.TABLE_NAME + " " + userTable1...

so this should do the trick, at least for me it does:

Select * From Events 
Inner Join Users Users1 On Users1.User_Id = Events.Signed_In_User_Id 
Inner Join Users Users2 On Users2.User_Id = Events.Owner_User_Id 
Where Events.Event_Code = 10231009