Joining multiple records in a CursorAdapter

Question

I'm trying to build a ListView that represents a list a recipes. I get the concept of using a SimpleCursorAdapter to accomplish that.

However, in addition to showing the recipe image & title for each item, I'd like to show a comma-separated string of ingredients associated with the recipe. Given that a cursor is only for one table (or view), I'm not quite sure how to approach this.

Relevant tables/columns are 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 question lead me to believe there's not enough information about why I think a simple JOIN won't suffice. Given the table structure, a recipe can have multiple ingredients, not just one. That means if I try to perform a join for a single recipe, I'll end up with 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 would end up showing "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 comma-separated. i.e.:

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

Lambeth

This page offers a good introduction to SQL JOIN and that's what you will need in this case: w3schools.com/sql/sql_join.asp – Dealings

A Cursor is pretty much a wrapper around the result set returned by a database query. In other words: saying that a "cursor is only for one table", is not true. You should change your query to include the ingredients data for every recipe. Basically you'll want to do a join and select columns from both the recipes and ingredients table. – Goaltender

@EmanuelMoecklin @MH - Okay, so can either of you provide an answer that shows how to join one recipe column to multiple ingredient columns and only have it be one result as a comma-delimited list of ingredients? The way I see it, if I join recipes to ingredients, I'll end up with multiple rows for the same recipe instead of just one row. – Lambeth

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;

Dealings

Awesome, thank you!! For reference, here's more info on it along with a way to specify the separator: sqlite.org/lang_aggfunc.html – Lambeth