[thelist] basic SQL many-many optimization

[SBeam]

On Wednesday 16 July 2003 11:14 am, Tab Alleman wrote:
> 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? 

> 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%'
>

well then you have a different row for each title/genre combination and have 
to programatically combine the genres and eliminate redundant titles. Which 
would be OK, but when paging the result set, how do you get total # matches? 
so it doesn't seem much more elegant/efficient. (it might benchmark 
better...) 

> 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?

thats exactly what I want. What kind of recursive thing?