CODEWITHSUNDEEP
androidsqlsqlitejoin
Joe Rakhimov
Joe Rakhimov

Reputation: 5083

How to take values of joined tables with same column name?

MainActivity.java:

    private void displayListView() {
    cursor = myDatabase.getJoinedInfo(etSearch.getText().toString());

    String[] columns = new String[] { myDatabase.RE_VALUE,
            myDatabase.GL_VALUE, myDatabase.KE_VALUE };

    int[] to = new int[] { R.id.tvHiragana, R.id.tvMeaning, R.id.tvKanji };

    dataAdapter = new SimpleCursorAdapter(this, R.layout.wordonlist,
            cursor, columns, to, 0);

    ListView listView = (ListView) findViewById(R.id.lvWordlist);
    // Assign adapter to ListView

    listView.setAdapter(dataAdapter);
}

MyDatabase.java:

public static final String GL_TABLE="gloss";
public static final String GL_ID="id";
public static final String GL_LANG="lang";
public static final String GL_VALUE="value";

public static final String KE_ID="id";
public static final String KE_VALUE="value";

public static final String RE_ID="id";
public static final String RE_VALUE="value";

public static final String LOG_TAG="myLogs";

public Cursor getJoinedInfo(String lookingFor)
{
    Log.d(LOG_TAG, "DB: looking up info");

    SQLiteDatabase db=getReadableDatabase();

    Cursor cursor;
    String query;

    query="SELECT " +
            " e.id AS _id," +
            " ke.id AS _id," +
            " ke.fk," +
            " ke.value," +
            " re.id AS _id," +
            " re.fk," +
            " re.value," +
            " s.id AS _id," +
            " s.fk," +
            " g.id AS _id," +
            " g.fk," +
            " g.lang," +
            " g.value" +
            " FROM entry e" +
            "     INNER JOIN k_ele ke ON e.id = ke.fk" +
            "     INNER JOIN r_ele re ON e.id = re.fk" +
            "     INNER JOIN sense s ON e.id = s.fk" +
            "     INNER JOIN gloss g ON s.id = g.fk" +
            " WHERE g.value like '%"+lookingFor+"%' LIMIT 20";

    Log.d(LOG_TAG, "DB: query = \n" + query.replace(", ",",\n  "));
    cursor=db.rawQuery(query,null);
    Log.d(LOG_TAG, "DB: query complete");

    return cursor;      
}

I have 5 tables. 3 of them have column with name "value". Cursor is taking only one table's "value" and ignoring others. And all values which matches with search string, are similar! Please give some suggestion about how to solve this problem.

enter image description here

Upvotes: 1

Views: 1576

Answers (1)

JB Nizet
JB Nizet

Reputation: 691715

The whole point of the AS keyword is to assign another name to a column, that is unique among all the retrieved columns. You're only using it on some of them, and always assign the same column name. The query should look like

query = "SELECT " +
        " e.id AS e_id," +
        " ke.id AS ke_id," +
        " ke.fk as ke_fk," +
        " ke.value as ke_value," +
        " re.id AS re_id," +
        " re.fk as re_fk," +
        " re.value as re_value," +
        " s.id AS s_id," +
        " s.fk as s_fk," +
        " g.id AS g_id," +
        " g.fk as g_fk," +
        " g.lang," +
        " g.value as g_lang" +
        " FROM entry e" +
        "     INNER JOIN k_ele ke ON e.id = ke.fk" +
        "     INNER JOIN r_ele re ON e.id = re.fk" +
        "     INNER JOIN sense s ON e.id = s.fk" +
        "     INNER JOIN gloss g ON s.id = g.fk" +
        " WHERE g.value like '%"+lookingFor+"%' LIMIT 20";

That way, every column in the result set will have a different name. You should also improve your naming, because names like g, s,e, ke and re don't mean anything and make the code hard to read and understand.

Upvotes: 2

Related Questions