Thanks! Yup the LEFT JOIN is probably a cleaner syntax over the nested query..
On Thu, Aug 12, 2010 at 1:37 AM, Thom Brown <[email protected]> wrote: > On 12 August 2010 09:16, Mike Christensen <[email protected]> wrote: >> Damn I'm the master at posting stuff then figuring it out like 5 >> seconds later.. Is there an approach better than this? >> >> select RecipeId, Rating, array_to_string(ARRAY(select Tag from >> RecipeTags where RecipeId = R.RecipeId), ' ') from Recipes R; >> > How about this: > > SELECT recipes.recipeid, recipes.name, > array_to_string(array_agg(recipetags.tag), ' ') > FROM recipes > LEFT JOIN recipetags ON recipes.recipeid = recipetags.recipeid > GROUP BY recipes.recipeid, recipes.name; > > Although in 9.0 you'll be able to do this: > > SELECT recipes.recipeid, recipes.name, string_agg(recipetags.tag, ' ' > ORDER BY recipetags.tag) > FROM recipes > LEFT JOIN recipetags ON recipes.recipeid = recipetags.recipeid > GROUP BY recipes.recipeid, recipes.name; > > -- > Thom Brown > Registered Linux user: #516935 > -- Sent via pgsql-general mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
