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?

 

[rpstewart]

All of the above is correct. If a film has 2 genres you'll get two rows back, there's nothing you can do about it.

What were you wanting to get back?

 

[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>
	");
}

 

[rpstewart]

Well you've got two options

1) Change the data model so that you've got a got a many to one relationship between films and genres (ie a film only has 1 genre)

2) Change the query to concatenate the genres into a comma separated list

Option 1 is the simplest, I'm struggling to come up with a solution for option 2, which is a tad embarrasing considering this is what I do for a living....

 

[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..

 

[rpstewart]

Maybe Saturday night isn't the best time to think about this kinda thing..

Tell me about it. I can thoroughly recommend the 1999 Castillo San Lorenzo Rioja if you can still pick up a bottle anywhere .

If I come up with anything tomorrow I'll let you know.

 

[heavy-rotation]

I don't think "Option 2" is possible actually, without using a stored procedure or something. I'm guessing this is MySQL though.

You can either do it the way you suggested though, using a seperate query to extract the genres for each entry, or something like this:

$lastFilm = "";
while ($row = mysql_fetch_row($rs))
{
	if ($lastFilm != $row[0])
	{
		if ($lastFilm != "")
		{
			echo("</td></tr>");
		}
		$lastFilm = $row[0];
		echo("<tr>
				<td>$row[0]</td>
				<td>$row[1]</td>
				<td>$row[2]</td>
				<td>");
	}
	else
	{
		echo(", ");
	}
	echo($row[3]);
}
echo("</td></tr>");

I'm not sure if that bit inserting commas as dividers will work as intended (not tested this), but you get the idea.

 

[matja]

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

 

[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

 

[heavy-rotation]

Bah, seems working with Microsoft products too long has rotted my brain!