Help with a sql query

[toString]

It's a pretty simple set up, but the query is annoying me.
But I can't figure out how to display the Genre's that are related to each film.

SELECT f.FilmID, f.Title, r.Rating, g.Genre
FROM Film f
INNER JOIN Film_rating r ON f.RatingID = r.RatingID
INNER JOIN Film_Genre fg ON fg.FilmID = f.FilmID
INNER JOIN Genre g ON fg.GenreID = g.GenreID

FilmID  Title          Rating       Genre  
1        Toy Story     U         Animated 
1        Toy Story     U         Childrens/Kids/Family

It works... But I don't want two rows. So I'm obviously doing it wrong. But I have no ideas how to fix it.

Any ideas?

 

[toString]

Ahh right, well this is what I want on my webpage:

So what would be the best way of doing this?

I'm hoping it's not something crazy like calling a function which runs a seperate query from within my while loop:

while ($row = mysql_fetch_row($rs)) {
// *** noes -->     $filmGenres = getGenres($row[3]);
	echo("
		<tr>
			<td>$row[0]</td>
			<td>$row[1]</td>
			<td>$row[2]</td>
			<td>$filmGenres</td>
			<td><a href=\"$detailScript?id=$row[0]\">Edit</a></td>
		</tr>
	");
}

 

[toString]

Hehe, thanks for trying though!

Option 1, isn't an option for me, unfortunately.
Other than doing what I suggested before - an extra query for every row returned - I'm at a loss.
Maybe Saturday night isn't the best time to think about this kinda thing..

 

[toString]

if its MySQL, use a grouping by the film primary key (filmid) and use a group_concat() transform function on the selected column :

SELECT f.FilmID, f.Title, r.Rating, group_concat(g.Genre) as genre
FROM Film f
INNER JOIN Film_rating r ON f.RatingID = r.RatingID
INNER JOIN Film_Genre fg ON fg.FilmID = f.FilmID
INNER JOIN Genre g ON fg.GenreID = g.GenreID
group by f.FilmID

That works perfectly! Many thanks!

Looks like there's quite a lot of mysql keywords that I never knew of after starting out on SQL Server.
I used:
CONCAT(val1 , ' ', val2 ) AS val3
earlier as my old fav of
val1 + ' ' val2 AS val3
wasn't working.

Thanks to everyone who tried