Query Help

Hello ppl,
Let me get straight to the point. I have three tables: -
1.)Movies-contains fields Movie_Id(PK) and Movie_Title
2.)Actors-contains fields Actor_Id(PK) and Actor_Name
3.)MoviesActors-contains fields Movie_Id(FK) and Actor_Id(FK)

PK->Primary Key
FK->Foreign Key

An Actor entity and a Movie entity share a many-to-many relationship between each other. Hence the need for the association table, MoviesActors.

Now I need to get a result set in the following format:-
Movie Id,Movie Title,Cast......where CAST is a comma-delimited string comprising the names of actors associated with the movie.

An example of the result set would be..

Movie Id Movie title Cast
---------- ---------------------- -----------------------------------
01 About A Boy Hugh Grant, Rachel Weisz

Hugh Grant and Rachel Weisz would be two separate entries in the Actors table.
Please Help!!

24-7-365 DUDE

Create a stored procedure that does the following:

1. Create a CURSOR to go through the rows of the Movies table.
2. Create a temp table (say #movie_cast)
3. For each row fetched by the cursor, insert the id and name into #movie_cast.
4. Next, populate an actors temp table containing the actors for the movie.
5. Select all the rows from the actors temp table and concatenate them. Update the corresponding row in #movie_cast with the concatenated string.
6. Skip back to step 3, if there are more rows to be fetched by the cursor.
7. Select all rows from #movie_cast (to return them),
8. Drop all temp tables.

Hope this helps.

Thank you ! Your solution workes Scorpion. however, I tried making a VIEW with it, but the effort failed because I cant use the technique of using Cursors in defining a VIEW. How do i pull out the rabbit out of the hat now? A VIEW is what I ideally need.

Please Help!Thanking you in advance,
24-7-12-365 DUDE

Make a stored procedure instead and make the SELECT as the last statement. It'll work just like a view and will automatically trash the temp table when the connection terminates.
-Dave

Thanx Dave!!But I already thought of that.However, I am glad that I thought of what you said as it would mean that i am learning.Finally, the DUDE has begun his journey!!
Yours Sincerely,
24-7-12-365 DUDE