From: | Mike Christensen <mike(at)kitchenpc(dot)com> |
---|---|
To: | Thom Brown <thom(at)linux(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Converting join'ed rows into a comma or space delimited list |
Date: | 2010-08-12 09:24:48 |
Message-ID: | AANLkTinrnwWHmdfDmEV5hAhwnHYzHmhgOCz2BuKNWGXu@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <thom(at)linux(dot)com> wrote:
> On 12 August 2010 09:16, Mike Christensen <mike(at)kitchenpc(dot)com> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Torsten Zühlsdorff | 2010-08-12 09:28:32 | Re: InitDB: Bad system call |
Previous Message | Torsten Zühlsdorff | 2010-08-12 09:04:42 | Re: InitDB: Bad system call |