August 28th, 2003, 06:03 AM
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)
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.
August 28th, 2003, 01:24 PM
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.
Up the Irons
What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
"Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
Down with Sharon Osbourne
"I wouldn't hire a butcher to fix my car. I also wouldn't hire a marketing firm to build my website." - Nilpo
August 29th, 2003, 01:58 AM
Making the query a VIEW
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,
August 29th, 2003, 08:33 AM
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.
August 29th, 2003, 08:41 AM
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!!