TableAdapter

Hi all,
I have three tables; in fact they are two that I will present in a later step. What I want is to use the table adapter but I am getting a problem.

Tables:
1) Movie
MovieID,MovieTitle,MovieGenre,MovieTrailer,MoviePoster,...
2) Cast
CastID,CastName,CastBIO,CastDOB,CastPicture,...
3) CastMovie
MovieID,CastID

The problem is that I want to use a formview by using the table adapter; this table adapter should retrieve the movie information from the movie table and retrieve the cast name from the cast table, so that we can have the movie with the entire cast name within it.
How to do that with the available tables, by the way I am using the objectDataSource?

if anyone have another tools that we can use to insert, delete and edit other than the formview just provide me with it

RegardsYou could use SQL JOINs, probably CROSS JOIN (http://www.java2s.com/Code/SQL/Join/CROSSJOINinMySQL.htm).
thanx for your fast reply, but i already made a cross table by joining the cast and movie; the table which is called castmovie. however this table will not work.
All I can think of, in that case you would need a column common to both Tables, and related to each other. But wait, somebody could prove me wrong!
SELECT M.MovieID, M.MovieTitle, M.MovieGenre, M.MovieTrailer, M.MoviePoster, C.CastID, C.CastName, C.CastBio, C.CastDOB, C.CastPicture FROM Movie M LEFT JOIN CastMovie CM ON CM.MovieID = M.MovieID LEFT JOIN Cast C ON C.CastID = CM.CastID

Or something similar. It will get you multiple rows per movie though.
thank you alot
Wouldn't that require you to have CastMovie table in your database?? you said that you will remove it later. :confused:
i want to be able to enter more than one cast per movie as well as i want to be able to delete the movie info without the cast info, thats why i think its required to have a third table (many to many relatiobship).

regards,