Visualization of data coming from a JOIN query SqLite in Android

Question

for the first time I stumbled upon the concept of INNER JOIN with SqlLite while programming for Android. I managed to get something back but the problem now is that I want to display the data in a List and I cannot display the information as I supposed to.

Here is the query that retrieve the data

cursor = database.rawQuery("SELECT matchesbet._id, hometeam, awayteam, gamble " +
                    "FROM matchesbet INNER JOIN gambles ON matchesbet.idmatch = gambles.idmatch " +
                    "WHERE matchesbet.idbet = ? GROUP BY matchesbet.idmatch ORDER BY matchesbet._id DESC", selectionArgs);

The data are used in a class which extends CursorAdapter in the bindView method

    TextView tvGamble = (TextView) view.findViewById(R.id.gamble_text_view);

    // Extract properties from cursor
    int gamble = cursor.getInt(cursor.getColumnIndex("gamble"));

    tvGamble.setText(String.valueOf(gamble));

Now in the DB I save multiple "gamble" for each match but the only thing that I can display is 1 gamble.

Db tables structure and sample data

MATCHESBET
    +-----+-------+---------+----------+-----------+--+
    | id  | idbet | idmatch | hometeam | awayteam  |  |
    +-----+-------+---------+----------+-----------+--+
    | 349 |  3000 |      27 | TeamA    | TeamZ     |  |
    | 350 |  2456 |      39 | TeamC    | TeamG     |  |
    | 351 |  2818 |      20 | TeamW    | TeamB     |  |
    +-----+-------+---------+----------+-----------+--+

    GAMBLES
    +-----+---------+--------+-------+
    | _id | idmatch | gamble | idbet |
    +-----+---------+--------+-------+
    | 349 |      27 | WIN    |  3000 |
    | 350 |      27 | LOST   |  3000 |
    | 351 |      27 | DRAW   |  3000 |
    | 489 |    1345 | WIN    |  1981 |
    +-----+---------+--------+-------+

Besed on the above data considering that I filter the Bet with ID = 3000 I would like to have a listItem with TeamA vs TeamZ (ID = 27) and in the same Row the string WIN, LOST, DRAW. Then iterate again through all the matches that have as IDBET = 3000

I'm not sure how to get out of this situation Hope someone can help Cheers

Answer 1

I believe what you want is as simple as :-

SELECT matchesbet._id, hometeam, awayteam, group_concat(gamble) AS gamble 
FROM matchesbet JOIN gambles ON  gambles.idmatch = matchesbet.idmatch
WHERE matchesbet.idbet = 3000 
GROUP BY matchesbet.idmatch 
ORDER BY matchesbet._id DESC;

i.e. Keeping the GROUP but instead of selecting column gamble selecting a column that is group_concat(gamble) AS gamble.

By default the values are separated by a ,. However you can specify another separator via a 2nd parameter e.g. group_concat(gamble,' - ') would give :-

You may find this link helpful Aggregate Functions