Stuck with android SQL design using SQLite and Group By

Question

have the following problem: Trying to display a list of attendees from a table. Table A is generated by "syncing" the data from a web source (HTML scraping) into table A.

Table A
Columns: eventID, userID, userName
Data:
1, 111, Harry
2, 222, Ben
3, 534, Helen
2, 534, Helen
2, 545, James

End Result
Im trying to get this displayed in an activity as follows:
Social 1: Attended by: Harry
Social 2: Attended by: Ben, Helen, James
Social 3: Attended by: Helen

How is the best way to do this?

My initial plan:
1) Run a SQL select (Query1) with Group By on eventID from table A.
2) Cycle through each result from Step 1.
3) For each result result from Query 1, select all rows in tableA with the current eventID (Called Query 2)
4) Cycle through result from step 3 Query 2, and for each result, add the user's name to a string buffer. At the end of Query 2 results, insert the eventID together with the resulting StringBuffer, into a new table:

Table B:
columns: eventID, userName
Data:
1, "Harry"
2, "Ben, Helen, James"
3, "Helen"

Then use the data from table B, together with a SimpleCursorAdapter, to populate a standard ListActivity with the data, resulting in a list of all events, and a comma separated list of attendees. And then each time Table A is regenerated, clear the data from table B, and repopulate it.

Question:
Is this the best way/neatest to do this? I dont really like the idea of having a temporary table just to do this.

Background: Im new to Android dev, and have only just taken up Java again since doing it at uni a few years ago. My strongpoint in programming lies in HTML, PHP, MySQL.

Extra info: The way data is collected for table A, is that a page is scraped which contains all the events for a particular user. I then aim to display this in the opposite way, by showing for each event, all the users that are attending.

I would appreciate some help/opinions on how to accomplish this. Many thanks!

Answer 1

Can't you try something like the following ?

SELECT 
    eventID,
    userID, 
    GROUP_CONCAT(userName, ",") AS users
FROM table_a
GROUP BY eventID

Thus you can get all the data with a single query.

See more about GROUP_CONCAT.