Using IN keyword in android sqlite

Question

I have two tables:

1) Employee -- _id, employee_name. 2) Salary -- _id, amount, emp_id. 

Sample data:

 Employee: 1 John 2 Rocky 3 Marry Salary: 1 500 1 //salary for John 2 400 1 //salary for John 3 600 2 //salary for Rocky 4 700 2 //salary for Rocky 5 350 3 //salary for Marry 

Now I want to search the salary table to see to whom I paid the salary. let's say if I look for “John” in the salary table, he should return rows 1 and 2 that are for John.

Here is what I am trying:

 String where = " emp_id in (select _id from Employee where employee_name like ? )"; String[] whereArgs = new String[] {"'%" + mFilter + "%'" }; Cursor c = getDB(mContext).query("Salary", null, where, whereArgs, null, null, null); 

But it does not return results. Please, help.

Update

I debugged the code and found that the following query is being executed in the cursor:

 SELECT * FROM Salary WHERE emp_id in (select _id from Employee where employee_name like ? ); 

Answer 1

Selection arguments are automatically used as strings. Change this:

 String[] whereArgs = new String[] {"'%" + mFilter + "%'" }; 

To that:

 String[] whereArgs = new String[] {"%" + mFilter + "%" }; (removed ' from your strings) 

With the additional ' there, it cancels the text and becomes ''%John%'' The builder automatically processes ' for the selection arguments.

EDIT
Modify your request as well:

 String sql = "SELECT * FROM Salary WHERE emp_id in (select _id from Employee where employee_name like ?)"; Cursor c = getDB(mContext).rawQuery(sql, whereArgs); 

EDIT 2

I recreated your setup with the class below, and all my code worked fine. I pulled John out of user and got both results. I believe that the problem is creating a database or you simply do not have data in your database. Use DDMS to pull out the database and open it using the SQLite browser. Check if there is any data in your database. If so, the table creation types do not match the select query. When I call GetMyValues ​​(), I get 2 records returned from the cursor.

 public class DataBaseHandler extends SQLiteOpenHelper { private static final String TAG = "DBHandler"; //Database VERSION private static final int DATABASE_VERSION = 2; //DATABASE NAME private static final String DATABASE_NAME = "test"; //DATABASE TABLES private static final String TABLE_SALARY = "Salary"; private static final String TABLE_EMP = "Employee"; //DATABASE FIELDS private static final String SalaryID= "_id"; private static final String SalaryEmpName = "employee_name"; private static final String EmpID= "_id"; private static final String EmpAmt = "amount"; private static final String EmpSalID = "emp_id"; //DATABASE TYPES private static final String INTPK = "INTEGER PRIMARY KEY"; private static final String INT = "INTEGER"; private static final String TEXT = "TEXT"; //CREATE TABLES private static final String CREATE_SALARY_TABLE = "CREATE TABLE " + TABLE_SALARY + "(" + EmpID + " " + INTPK + "," + EmpAmt + " " + INT + "," + EmpSalID + " " + INT + ")"; //CREATE TABLE Salary(_id INTEGER PRIMARY KEY,amount INTEGER,emp_id INTEGER) private static final String CREATE_EMPLOYEE_TABLE = "CREATE TABLE " + TABLE_EMP + "(" + SalaryID + " " + INTPK + "," + SalaryEmpName + " " + TEXT + ")"; //CREATE TABLE Employee(_id INTEGER PRIMARY KEY, employee_name TEXT) public DataBaseHandler(Context context){ super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_EMPLOYEE_TABLE); db.execSQL(CREATE_SALARY_TABLE); insertEmployeeValues(db); insertSalaryValues(db); } private void insertEmployeeValues(SQLiteDatabase db){ ContentValues values = new ContentValues(); values.put(SalaryEmpName, "John"); db.insert(TABLE_EMP, null, values); values.clear(); values.put(SalaryEmpName, "Rocky"); db.insert(TABLE_EMP, null, values); values.clear(); values.put(SalaryEmpName, "Marry"); db.insert(TABLE_EMP, null, values); values.clear(); } private void insertSalaryValues(SQLiteDatabase db){ ContentValues values = new ContentValues(); values.put(EmpAmt, 500); values.put(EmpSalID, 1); db.insert(TABLE_SALARY, null, values); values.clear(); values.put(EmpAmt, 400); values.put(EmpSalID, 1); db.insert(TABLE_SALARY, null, values); values.clear(); values.put(EmpAmt, 600); values.put(EmpSalID, 2); db.insert(TABLE_SALARY, null, values); values.clear(); values.put(EmpAmt, 700); values.put(EmpSalID, 2); db.insert(TABLE_SALARY, null, values); values.clear(); values.put(EmpAmt, 350); values.put(EmpSalID, 3); db.insert(TABLE_SALARY, null, values); values.clear(); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS " + TABLE_EMP); db.execSQL("DROP TABLE IF EXISTS " + TABLE_SALARY); onCreate(db); } public int GetMyValues(){ String mFilter = "John"; String[] whereArgs = new String[]{"%" + mFilter + "%"}; int count = 0; SQLiteDatabase db = this.getWritableDatabase(); String where = " emp_id in (select _id from Employee where employee_name like ? )"; Cursor c = db.query("Salary",null, where, whereArgs,null,null,null); count = c.getCount(); c.close(); return count; } } 

Link to the project:

Can you turn on? s in the selection, which will be replaced by the values ​​from selectionArgs so that they appear in the selection. values ​​will be linked as strings

Answer 2

Using a cursor is better, I think

Demo code:

  Cursor c = db.query(TABLE_CONTACTS, columns, KEY_MOBILE + " like '%" + mobno + "'", null, null, null, order); return c; 

It will return a value if an instance of this value is in the database !!