Combining multiple records in a CursorAdapter

Question

I am trying to create a ListView that represents a list of recipes. I get the concept of using SimpleCursorAdapter to accomplish this.

However, besides showing the image and the name of the recipe for each item, I would like to show a whole chain of ingredients related to the recipe. Given that the cursor is designed for only one table (or view), I'm not quite sure how to approach this.

Relevant tables / columns below:

 recipes ( _id integer primary key, name text not null ) ingredients ( _id integer primary key, name text not null ) recipes_ingredients ( _id integer primary key, recipe_id integer not null, ingredient_id integer not null ) 

Edit: The first two comments on this issue make me believe that there is not enough information on why I think a simple JOIN will not be enough. Given the structure of the table, a recipe may contain several ingredients, not just one. This means that if I try to combine for one recipe, I will get something like the following:

 SELECT r.name AS recipe, i.name AS ingredient FROM recipes r INNER JOIN recipes_ingredients ri ON ri.recipe_id=r._id INNER JOIN ingredients i ON ri.ingredient_id=i._id; recipe | ingredient ------------------+--------------------- Chocolate Milk | milk Chocolate Milk | chocolate syrup Chicken Parmesan | chicken breast Chicken Parmesan | italian breadcrumbs Chicken Parmesan | egg Chicken Parmesan | shredded mozzarella Chicken Parmesan | pasta sauce 

A simple cursor will eventually display “Chocolate Milk” twice and “Chicken Parmesan” five times. This is definitely not what I want. I want each of them to display once with all the ingredients separated by a comma. i.e:.

  recipe | ingredients ------------------+--------------------------------------------------------------------------- Chocolate Milk | milk, chocolate syrup Chicken Parmesan | chicken breast, italian breadcrumbs, egg, shredded mozzarella, pasta sauce 

Answer 1

What you need is group_concat: http://www.sqlite.org/lang_aggfunc.html

This should work:

 SELECT r._id, r.name AS recipe, group_concat(i.name, ",") AS ingredient FROM recipes r INNER JOIN recipes_ingredients ri ON ri.recipe_id=r._id INNER JOIN ingredients i ON ri.ingredient_id=i._id GROUP BY r._id;