> Sometimes, movies have more than one director, so the > association between movies and directors needs to be in its > own table, instead of the movies table. (Otherwise, you're > limited to some fixed number of directors per > film.) > > Directors may not be the best example. Think about producers, > cast, etc. > > Let's say I need to pull all producers for a movie. For a > movie that has three producers (aka three matches with a LEFT > OUTER JOIN), I'll get three rows -- all with the same movie, > but different producers. > > Jurassic Park | Kathleen Kennedy | 35 comments > Jurassic Park | Gerald R, Molen | 35 comments > Jurassic Park | Lata Ryan | 35 comments > > How can I list all of these producers within a single row? > > Jurassic Park | Kathleen Kennedy, Gerald R, Molen, Lata > Ryan | 35 comments
I can't say I like the idea of concatenating all those items into one. Look at the parsing difficulty you've gotten yourself into. Is "Molen" a director? Or is it "Gerald R"? Most databases don't have any functions for this grouping-concatenation, since it leaves you with a result set that has a field of arbitrary length. What about a movie with 500 actors? So, really, I prefer to order by the movie, then the director. While processing the result set, just be aware you might have duplicate consecutive movies. Then you can roll in the directors as you go. So much for that lecture. If you're willing to use the ALPHA mySQL 4.1, there is a method that promises to do what you want: http://www.mysql.com/doc/en/GROUP-BY-Functions.html, look at GROUP_CONCAT. I think that fits the bill, with the caveat that it might well not be ready for prime-time yet. Oh, and it won't work on (any) other databases. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
