SQL Table Joins

well no excuses for db design as i am a completer and utter Newbie at sql..

could someone give me an example of a better design?.. it would be a great help..

Ta

Thanks Ljamal,..

thats exactly what i want to do ..  but how?..

how do i add a new row per movie instead of adding new row per record?.

you definitely cant do what you describe in just a simple SQL statement, if its SQL server, you can use a cursor though (bear with me)

CREATE PROCEDURE [dbo].[GetMovieList]

AS

DECLARE @MovieID int, @Movie varchar(255), @Year int
DECLARE @Actors varchar(1000), @ThisActor varchar(200)

SET NOCOUNT ON
CREATE TABLE #tempList ( MovieID int, Movie varchar(255), [Year] int, Actors varchar(1000) )
DECLARE c1 CURSOR FOR
 SELECT movieID, title, [year] FROM dvd
OPEN c1
FETCH NEXT FROM c1 INTO @MovieID, @Movie, @Year
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @Actors = ''
 DECLARE c2 CURSOR FOR
  SELECT actor FROM actor WHERE actorID IN
   (SELECT actorID FROM movies WHERE movieID = @MovieID)
 OPEN c2
 FETCH NEXT FROM c2 INTO @ThisActor
 WHILE @@FETCH_STATUS = 0
 BEGIN
  IF @Actors <> ""
   BEGIN
    SET @Actors = @Actors + ', '
   END
  SET @Actors = @Actors + @ThisActor
 FETCH NEXT FROM c2 INTO @ThisActor
 END
 CLOSE c2
 DEALLOCATE c2

 INSERT INTO #tempList
  (MovieID, Movie, Actors) VALUES
  (@MovieID, @Movie, @Actors)
FETCH NEXT FROM c1 INTO @MovieID, @Movie, @Year
END

CLOSE c1
DEALLOCATE c1

SET NOCOUNT OFF
SELECT * FROM #tempList

GO