Select with two columns returning empty

Question

In a mobile application (Android), I have two tables. A login table (user and password) and another (simple registration). The APP has a registration screen, if the user does not have an account he creates at the moment and proceeds. If this user makes a registration and popular table (simple registration) and another user who has no account, create an account and access, he will access the data of the first user.
I tried to spin INNER JOIN but the result (select) returns empty. If I run without the inner join (select * from tabela) it returns the data normally.

public class PessoaDao extends SQLiteOpenHelper {   
    private static final String NOME_BANCO = "TrainingBD";
    private static final int VERSION = 1;
    private static final String TABELA = "pessoa";

    private static final String ID = "_id";
    private static final String NOME = "nome";
    private static final String IDADE = "idade";
    private static final String CELULAR = "celular";
    private static final String DATACADASTRO = "dataCadastro";
    private static final String TIPOTREINO = "tipoTreino";
    private static final String PESO = "peso";
    private static final String ALTURA = "altura";
    private static final String OBSERVACAO = "observacao";
    private static final String USUARIOAUTENTICADO = "usuarioautenticado";

    private static final String TAG = PessoaDao.class.getSimpleName();
    private static final String TABELAUSUARIO = "users";
    private static final String USERS_ID = "_id";
    private static final String USERS_LOGIN = "login";
    private static final String USERS_SENHA = "senha";

    public PessoaDao(Context context) {
        super(context, NOME_BANCO, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        //tabela pessoas (formulario de cadastro)
        String sql = "CREATE TABLE IF NOT EXISTS "+TABELA+" (" +
                ""+ID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+
                ""+NOME+" TEXT, "+
                ""+IDADE+" INTEGER, "+
                ""+CELULAR+" TEXT, "+
                ""+DATACADASTRO+" TEXT, "+
                ""+TIPOTREINO+" TEXT, "+
                ""+PESO+" TEXT, "+
                ""+ALTURA+" TEXT, "+
                ""+OBSERVACAO+" TEXT, "+
                ""+USUARIOAUTENTICADO+", INTEGER," +
                " FOREIGN KEY ("+USUARIOAUTENTICADO+") REFERENCES "+TABELAUSUARIO+"("+USERS_LOGIN+"));";

        db.execSQL(sql);

        String CREATE_TABLE_USERS = "CREATE TABLE IF NOT EXISTS " + TABELAUSUARIO + "("
                + USERS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + USERS_LOGIN + " TEXT,"
                + USERS_SENHA + " TEXT);";

        db.execSQL(CREATE_TABLE_USERS);
    }

    //select para chamar os dados que estão no banco
    public ArrayList<Pessoa> selectAllPessoa(){
        String colunas = "SELECT * FROM " + TABELA + "," + TABELAUSUARIO + " WHERE " + TABELAUSUARIO + "." + USERS_LOGIN + " = " + TABELA + "." + USUARIOAUTENTICADO;

        //SE EU USAR ESSE MODO ELE RETORNO NORMALMENTE
        //OS DADOS JA CADASTRADOS
        //String colunas =  " Select * from " + TABELA  ;

        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(colunas, null);

        ArrayList<Pessoa> listPessoa = new ArrayList<Pessoa>();
        cursor.moveToFirst();

            while(cursor.moveToNext()){
                Pessoa pessoa = new Pessoa();

                pessoa.setId(cursor.getInt(0));
                pessoa.setNome(cursor.getString(1));
                pessoa.setIdade(cursor.getString(2));
                pessoa.setCelular(cursor.getString(3));
                pessoa.setDataCadastro(cursor.getString(4));
                pessoa.setTipoTreino(cursor.getString(5));
                pessoa.setPeso(cursor.getString(6));
                pessoa.setAltura(cursor.getString(7));
                pessoa.setObservacao(cursor.getString(8));
                pessoa.setUsuarioautenticado(cursor.getString(9));

                listPessoa.add(pessoa);
            }
        cursor.close();
        return listPessoa;
    }
}

---

Yes, when the user logs into the app it will only show what he added or what he edit in Listview.

It wasn’t supposed to work if I put this line of code that you posted on my method that already does the search

//select to call the data that are in the database public Arraylist selectAllPessoa(){

   String colunas = "SELECT * FROM " + TABELA + " WHERE " + USERS_ID + "=" + ID;

    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(colunas, null);

    ArrayList<Pessoa> listPessoa = new ArrayList<Pessoa>();
    cursor.moveToFirst();

        while(cursor.moveToNext()){

            Pessoa pessoa = new Pessoa();

            pessoa.setId(cursor.getInt(0));
            pessoa.setNome(cursor.getString(1));
            pessoa.setIdade(cursor.getString(2));
            pessoa.setCelular(cursor.getString(3));
            pessoa.setDataCadastro(cursor.getString(4));
            pessoa.setTipoTreino(cursor.getString(5));
            pessoa.setPeso(cursor.getString(6));
            pessoa.setAltura(cursor.getString(7));
            pessoa.setObservacao(cursor.getString(8));
            pessoa.setUsuarioautenticado(cursor.getInt(9));

            listPessoa.add(pessoa);

        }
    cursor.close();
    return listPessoa;
}

This other snippet of the code is where I populate Listview, calling Arraylistpessoa where Select has already been modified, but the empty Listview appears.

public void fillerList(){

    PessoaDao pessoaDao = new PessoaDao(CadastroActivity.this);
    arrayListPessoa = pessoaDao.selectAllPessoa();

    if(listView != null){
        arrayAdapterPessoa = new ArrayAdapter<Pessoa>(CadastroActivity.this,
                android.R.layout.simple_list_item_1,
                arrayListPessoa);
        listView.setAdapter(arrayAdapterPessoa);
    }
    pessoaDao.close();
}

---

enabling FK

@Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        if (!db.isReadOnly()){
            db.execSQL("PRAGMA foreign_keys=ON;");
        }

    }

And I already traded the TEXT field for INTEGER at FK which was incorrect when creating Table.

"" + ID_USUARIO + ", INTEGER, " +
                    " FOREIGN KEY (" + ID_USUARIO + ") REFERENCES " + TABELAUSUARIO + "(" + USERS_ID + "));";

However this error arose that when I will save the data coming from the app. I’m even trying to find out

android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787)

Answer 1

The relationship between the tables appears to be 1:1 (or will your application allow 2 users to have the same login user?). If it is 1:1, why create 2 tables? I am not seeing any gain in this implementation (separate personal data from access data). You could keep everything in one table and avoid unnecessary joins.

1:1 relationships make more sense when you have an application that wants to consume data from another application’s table and you would like to add extra data (columns) that do not exist in the original table and your application has no management over it, then the solution is to create a new table with the columns needed in your application by placing a foreign key referencing the source table.

I’m sorry I ran away from the original topic, but it was just a suggestion. With respect to the question, Join probably does not work because you are comparing an INTEGER field with another one that is TEXT. Even the own FK you created should give problems because of this if you enable the constraints in the BD (In SQLITE they are not enabled by default, you have to enable manually on the database startup).

Answer 2

Ola, Do you want to return data only from the right logged in user? I see no need to do Join....

Voce already has the method that returns all people and theoretically Voce already has the data of the authenticated user, so create a method that returns only the person where the id matches with the logged user, something like

public Pessoa pegaPessoa(string id){

    String sql = "SELECT * FROM " + TABELA + " WHERE " + USERS_ID + "=" + id

//.....restante do codigo

}