Android sqlite Select Join Where

Question

I am very new and trying to learn how to use Select, Join, and Where with sqlite in android. Ideally this is what I want to return from the Database:

People who have the role id of 4 (Ranged):

select people.[name]
from people
join people_role
on people.[id] = people_role.[people_id]
where role_id = 4

What would the code be for returning People that have the role_id of 4?

@zapl I am not sure how to start, my buddy who is working on the back end gave me this to work with. Would I make a string and put the "select people.[name] ... role_id = 4" as is in a string and pass it in rawQuery()? Or is there some modification to string I would need to do? — JoeyL, Sep 03 '12 at 13:25

score 1 · Accepted Answer · edited May 23 '17 at 11:49

1- Create your database using SQLiteBrowser.

2- Keep the Database in assets folder

3- Follow this solution.

4- Find getTestData() in TestAdapter calss & replace it with :

 public Cursor getTestData() 
 { 
     try 
     { 
         String sql ="select people.[name]  
           from people  
           join people_role  
           on people.[id] = people_role.[people_id]  
           where role_id = 4 "; 

         Cursor mCur = mDb.rawQuery(sql, null); 
         if (mCur!=null) 
         { 
            mCur.moveToNext(); 
         } 
         return mCur; 
     } 
     catch (SQLException mSQLException)  
     { 
         Log.e(TAG, "getTestData >>"+ mSQLException.toString()); 
         throw mSQLException; 
     } 
 }

Android sqlite Select Join Where

1 Answers1