I am almost 100% sure I have done this before but I have been trolling through my code all over the place and can't find a working line.

I have this query:

******
select
c.*,
(select g.genre_name from clip_has_genre chg left join clip_genre g on chg.genre_id = g.genre_id where chg.clip_id = c.clip_id) as genre_name

from
clip c
******

Clip has an clip_id field

Clip_Genre has an genre_id field

Clip_has_genre is just clip_id, genre_id and is used as a many to many relationship.

The above query works when a clip only has one genre, as soon as there is more then one genre to a clip I get the error:
"more than one row returned by a subquery used as an expression"

Is there a way to get all the genres into 1 column? I only want 1 row per clip, even if has multiple genres.

Using Postgres 8.1.11

Am I out to lunch on this?

[edited by: Demaestro at 6:11 pm (utc) on Mar. 25, 2008]