Thread: Query Help

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    UAE
    Posts
    3
    Rep Power
    0

    Question 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
  2. #2
  3. Banned ;)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    Woodland Hills, Los Angeles County, California, USA
    Posts
    9,638
    Rep Power
    4247
    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
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    UAE
    Posts
    3
    Rep Power
    0

    Question 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,
    24-7-12-365 DUDE
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2003
    Location
    New York
    Posts
    49
    Rep Power
    12
    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
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    UAE
    Posts
    3
    Rep Power
    0
    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

IMN logo majestic logo threadwatch logo seochat tools logo