[thelist] basic SQL many-many optimization

[Tab Alleman]

SBeam wrote:

> 
> So, when a search for titles returns many results, what is the most
> optimal way of getting the list of genres for each title? It is
>      working now as I do: SELECT g.id, genre_name FROM titles_genres
>      tg, genres g WHERE tg.title_id = $title_id AND g.id = tg.genre_id
> for _each_ row returned by the title search query. With 100s of
> results at times this seems ridiculous. This is a basic many-many
> relationship but I can't seem to find a definitive answer.

Well for one thing, why don't you return the genres in your title search
query:

SELECT t.TitleName, g.GenreName
FROM Titles t
INNER JOIN Titles_Genres tg ON t.TitleID=tg.TitleID
INNER JOIN Genres g ON g.GenreID=tg.GenreID
WHERE t.TitleName LIKE '%SeachStr%'

Or if you wanted your result set to come out like this:

Title		Genres
T1		g1, g2, g3
T2		g1, g2
T3		g1, g2, g3, g4, g5

Then maybe you could do some kind of recursive thing?