CODEWITHSUNDEEP
javaandroidsqlite
Gor Grigoryan
Gor Grigoryan

Reputation: 78

Android Sqlite Inner Join

I'm trying to get this

enter image description here

I have 2 tables - Words Table (ID, NAME, TRANSLATION) and WordSynonym Table(synonym_id1, synonym_id2).

All my words are going into first table, and when I need I am creating connection between words via second table.

Now I need to make method to get all synonyms by specific id.

For that I have tried this.

@Override
public ArrayList<Word> getAllSynonymsByWord(int id) {
    ArrayList<Word> synonyms = new ArrayList<>();
    Cursor c = database.rawQuery("select id, word, translation from " + WordsTable.TABLE_NAME + " inner join " + WordSynonymTable.TABLE_NAME + " on " + WordSynonymTable.COLUMN_WORD_ID + " = " + id, null);
    c.moveToFirst();
    while (!c.isAfterLast()) {
        Word s = new Word();
        s.setId(c.getInt(0));
        s.setName(c.getString(1));
        s.setTranslation(c.getString(2));
        synonyms.add(s);
        c.moveToNext();
    }
    c.close();
    return synonyms;
}

Parameter ID is id of the word I need (id = 1 in picture) I want to return words with id = 2 and id = 3 for example but this is not working properly.

What should I change? If you need another part of code comment and I will add.

Upvotes: 0

Views: 1313

Answers (1)

CL.
CL.

Reputation: 180010

You need to join the words table two times:

SELECT W2.ID,
       W2.Name,
       W2.Translation
FROM Words
JOIN WordSynonym ON Words.ID = WordSynonym.Synonym_ID1
JOIN Words AS W2 ON WordSynonym.Synonym_ID2 = W2.ID
WHERE Words.ID = ?;

Upvotes: 1

Related Questions