Displaying Data in Datagrid from a Normalized Set of Tables

Ok, I'm fairly new to .NET and even newer to the whole database concept. But, don't run away yet, I'm no idiot and I shouldn't have too hard of a time understanding your responses if you're kind of enough to give them. That being said, here's my dilemma:

I'm trying to make a database of all the movies I own, the actors in them and the genre (s) they belong to. I have a set of tables that are in the 2NF (I think). I have a movies table, an actors table, a genres table, and two tables called movies_actors and movies_genres with primary-foreign key relationships to pull it all together (e.g. movie_id 1 has two entries in movies_genres, one for Action and one for Drama).

My problem arises that when execute my monster query to pull ALL the data on one movie, I get a row returned for every combination of Genres and Actors in a movie. Example:

movie_id movie_title comments actor_first actor_last genre_name

1 Casino blah blah Robert DeNiro Action

1 Casino blah blah Robert DeNiro Drama

1 Casino blah blah Joe Pesci Action

1 Casino blah blah Joe Pesci Drama

And here's the query that produced that:

1SELECT movies.movie_title, movies.comments, actors.actor_first,2actors.actor_last, genres.genre_name3FROM moviesINNERJOIN movies_actorsON movies.movie_id = movies_actors.movie_id4INNERJOIN actorsON movies_actors.actor_id = actors.actor_id5INNERJOIN movies_genresON movies_genres.movie_id = movies.movie_id6INNERJOIN genresON movies_genres.genre_id = genres.genre_id

So, I want to put all the actors for one movie into the same cell in the datagrid (same with the genres) and still keep it sortable by actor or genre. Is this possible with the .NET 2.0 datagrid? Do I have some fundamental misunderstanding of how my tables should be structured? Am I just really far off and acting like a n00b?

Can you provide some sample data from each of the tables (preferably with INSERT scripts) and expected output so it makes it easier for us to understand what you want and what you are doing to get what you want.

|||

Hi,

Your design and query looks ok, and query works as it supposed to, but I think your goal is a little bit different from what you are getting form your query.

To make it work as you want you will need to query only movie table first retrieving titles, than your data grid should have two template fields for actors and for genres which could contain a nested databound controls inside like a repeater or dataview, and those controls should have their own datasources that would rely on separate queries. For example:

first (master) query would look like this: select movie_id, movie_title, comments from movies.

the query for list of actors: select actor_first, actor_last from actors join movies_actors on movies_actors.actor_id = actors.actor_id where movies_actors.movie_id = @.movieId (this is a parameter for binding your nested data control that I mentioned about before)

and you would have similar query for genres.

|||

Ok, here's what a few basic INSERT's would look like on each of the tables:

movies table:

INSERT INTO movies (movie_id, movie_title,year, comments)
VALUES (1,'Casino', 1995,'Pretty cool movie')

actors table:

INSERT INTO actors (actor_id, actor_first, actor_last)--I think I might actually combine actor_first and actor_last
VALUES (1,'Robert','DeNiro'), (2, 'Joe' , 'Pesci')

movies_actors

INSERT INTO movies_actors (movie_id, actor_id)
VALUES (1, 1), (1,2)
genres
INSERT INTO genres (genre_id, genre_name)
VALUES (1,'Action'), (2,'Drama')
movies_genres
INSERT INTO movies_genres (movie_id, genre_id)
VALUES (1, 1), (1,2)
 So, that's just a little, but hopefully that's enough that you see how the tables are structured? If not, let me know and I can certainly elaborate further.
I'd like the output of the table to look something like this:
movie_idmovie_titleyearcommentsactorsgenres
1 Casino1995Cool movieRobert DeNiro,Action, Drama
 Joe Pesci
And, preferably, I'd still like to be able to sort by genre or actor, so if you sorted by Action this movie would still come up (i.e. you wouldn't need to sort by movies that are action AND drama).I guess if I wasn't such a stubborn person I'd have just said to hell with the normalized tables and put this all in one table (or I would have just bought software that does the same thing).
But, I know my table structure is supposed to be the way to do it ... like if I have to update the spelling of an actor's name this will cause the least trouble. (edit: yuck. Nuked the styling in the last part here somehow. Sorry.)
 
 

|||

--Prepare the tables and insert sample dataCreate table movies (movie_idint, movie_titlevarchar(100), [year]int, commentsvarchar(100))INSERT INTO movies (movie_id, movie_title, [year], comments)VALUES (1,'Casino', 1995,'Pretty cool movie') goCreate table actors (actor_idint, actor_firstvarchar(100), actor_lastvarchar(100))INSERT INTO actors (actor_id, actor_first, actor_last)Select 1,'Robert','DeNiro'unionallselect 2,'Joe' ,'Pesci' goCreate table movies_actors (movie_idint, actor_idint)INSERT INTO movies_actors (movie_id, actor_id)Select 1, 1unionallselect 1,2 gocreate table movies_genres (movie_idint, genre_idint )INSERT INTO movies_genres (movie_id, genre_id)Select 1, 1unionallselect 1,2create table genres (genre_idint, genre_namevarchar(100))INSERT INTO genres (genre_id, genre_name)Select 1,'Action'unionallSelect 2,'Drama'gocreate table movies_genres (movie_idint, genre_idint )INSERT INTO movies_genres (movie_id, genre_id)Select 1, 1unionallselect 1,2create table genres (genre_idint, genre_namevarchar(100))INSERT INTO genres (genre_id, genre_name)Select 1,'Action'unionallSelect 2,'Drama'Go--Create the required functionsCreate function dbo.fnGetActors( @.Movieidint)ReturnsVarchar(500)AsBeginDeclare @.Actorsvarchar(500)Set @.Actors =''Select @.Actors = @.Actors +', ' + (A.actor_first +' ' + A.actor_last )From movies_actors MAJOIN Actors Aon MA.actor_id = A.actor_idWHERE MA.movie_id = @.MovieidReturnRIGHT(@.Actors,LEN(@.Actors) - 1 )EndGoCreate function dbo.fnGetGenres( @.Movieidint)ReturnsVarchar(500)AsBeginDeclare @.Genresvarchar(500)Set @.genres =''Select @.genres = @.genres +', ' + genre_nameFrom movies_genres MGJOIN genres GON MG.genre_id = G.genre_idWHERE MG.movie_id = @.MovieidReturnRIGHT(@.genres,LEN(@.genres) - 1 )End--Write the queryselect M.* , Actor = dbo.fnGetActors(M.movie_id ) , Genres = dbo.fnGetGenres(M.movie_id)from movies M

|||

First off, thanks a ton for the responses guys!

Robert, I understand what you're saying. I guess I just didn't know I could make a nested control with its own datasource. Is it possible you have a link to an example of something like this? I've tried a couple searches but I think I have my terminology a little mixed up ...

NDinakar, IthinkI understand your code, but what would that output look like? Would it really put both matching actors within the same cell?

|||

Hi,

Dinakar's idea is to put everything together on the database layer and it would perform much better since you would get what you want with only one database connection.

Nesting data controls gives you more flexibility in terms of presentation and data manipulation on the web form (for example actor names could be presented as links that would point to a page showing their biography etc.), however since each control has its own datasource the select statements for actors and genres would be executed for each movie row in the gridview.

I do not have any example of nested controls, but if you send me an sql script for creating your tables I could produce a simple example - it's very easy.

|||

newmanium:

NDinakar, IthinkI understand your code, but what would that output look like? Would it really put both matching actors within the same cell?

Well, why dont you give it a try. Promise, the script will not kill your server. I spent 20 mins creating the scripts. It will take you a fraction of that to cut/paste the script and check it out.

|||

I actually did envision having more control over my presentation on the web form, so perhaps the nested control would work better for me. I was hoping to have a hyperlink on every actor's name so I could click on it and execute a new query for just the movies containing that actor.

But yeah, if you could give me a simple example that would tremendous. Here's what my tables look like:

CREATETABLE`actors`(

`actor_id`INT(5)NOTNULLAUTO_INCREMENTPRIMARYKEY,
`actor_name`VARCHAR(50)NOTNULL

)ENGINE=innodb;

CREATE TABLE ' movies' (
'movie_id' INT (5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
'movie_name' VARCHAR (50) NOT NULL,
'comments' TEXT NOT NULL,
'year' INT (4) NOT NULL
) ENGINE = innnodb;

CREATE TABLE 'movies_actors' (
'movie_id' INT (5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
'actor_id' INT (5) NOT NULL
) ENGINE = innodb;

CREATE TABLE 'genres' (
'genre_id' INT (5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
'genre_name' VARCHAR (50) NOT NULL
) ENGINE = innodb;

CREATE TABLE 'movies_genres' (
'genre_id' INT (5) NOT NULL,
'movie_id' INT (5) NOT NULL
) ENGINE = innodb;

P.S. I typed that by hand just now without a client, so I apologize if it has an error or two.

|||

And Dinakar, I will try out your script :) I'm at work right now so I can't try it out very well, but I very much appreciate your time.