Movie Database SQL Code Help

[jdm]

I'm working on a personal movie database project to help keep track of my movie collection, but I'm having issues and I was wondering if it was even possible and if so how to go about doing this in the first place. I want a way to get an overall list of everything. I have figured out how to get a list of the movies with their genres and a list of movies with their formats, but I want to combine the two.

My tables:

   Movies:  Code, Title, Alt_Title, Type, Plot, Num_Eps, Duration, Catagory, Image
        Code = PK
    Formats:  Code, Format
        Code = PK
    Genres:  Code, Genre, Descriptopin
        Code = PK
    MovieFormats:  MovieID, FormCode, Num_Discs, Locations
        MovieID & FormCode = PK
    MovieGenres:  MovieID, GenCode
        MovieID & GenCode = PK

   FK:
        MovieFormats:  MovieID & FormCode
        MovieGenres:  MovieID & GenCode

 Example:
     format sql:
          select DISTINCT m.Title, m.Type, m.Num_Eps, m.Duration,f.Format
                from MOVIES m
                inner join MOVIEFORMATS mf
                on m.Code=mf.MovieCode
                inner join FORMATS f
                on f.`Code`=mf.FormCode;

   output:
        Ghost in the Shell  Movie   1   01:23:00    Bluray
        Ghost in the Shell  Movie   1   01:23:00    DVD
        Summer Wars Movie   1   01:54:00    DVD

  genres:
    select Title, Type, Num_Eps, Duration ,g.Genre
            from MOVIES m inner join MOVIEGENRES mg
            on m.Code=mg.MovieCode
            inner join GENRES g 
            on g.`Code`=mg.GenCode;



 output:

   Ghost in the Shell   Movie   1   01:23:00    Cyberpunk
   Ghost in the Shell   Movie   1   01:23:00    Mecha
   Ghost in the Shell   Movie   1   01:23:00    Police
   Ghost in the Shell   Movie   1   01:23:00    Psychological
   Ghost in the Shell   Movie   1   01:23:00    Sci-Fi
   Summer Wars  Movie   1   01:54:00    Comedy
   Summer Wars  Movie   1   01:54:00    Sci-Fi

I want something that could combine the two, but so far when I try to combine them I get duplicate of everything. Is there a way to go about doing this?

Thakns for the help.

P.S. If you think I need to redesign my database I'm up for doing that if it would work and make since to do it.

[pixelsoul]

I don't really see anything wrong with the db design, might have done some stuff different, but it still looks okay.

What you probably need is GROUP_CONCAT which will allow you to combine the genres for the movie into one cell. Then you would do a GROUP BY Title at the end.

For example:

select Title, Type, Num_Eps, Duration , group_concat(g.Genre separator ', ')

from MOVIES m inner join MOVIEGENRES mg
on m.Code=mg.MovieCode
inner join GENRES g 
on g.`Code`=mg.GenCode;

group by Title

Documentation for group_concat:
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

[Reverend Jim]

I have no comments on the design but I thought you might be interested in My Movie API. I use it from vb.net to retrieve movie info. You might find it useful if you are currently populating the database manually.

[pixelsoul]

I have no comments on the design but I thought you might be interested in My Movie API. I use it from vb.net to retrieve movie info. You might find it useful if you are currently populating the database manually.

Thanks @Reverend Jim cool stuff. At one time I was building my own internal media site to stream my movie collection and was using http://www.themoviedb.org/documentation/api which was pretty cool too. I quit working on it though because of time constraints and other responsabilities :(

[Reverend Jim]

I had considered a movie database but I ended up making a more general tool. I wrote an app named Comment.exe. In the left panel it shows a list of all files in the current folder. In the right panel is any comment associated with the currently selected file (blank if none). Any comment text can be entered and is saved into an alternate data stream for that file (for example, if the file is named splunge.txt then the comment is saved in splunge.txt:comment). Files that have comments are shown in the left panel in bold. It also has a search feature to search comments in all files in the current folder.

Because I use this mostly for adding movie comments, double clicking on a file causes a query to imdb for the selected file to fetch plot and cast data.

[jdm]

Sorry for not posting sooner, but end of semester test just kept me busy. I did finish up my database design besides a few tweaks. I also got the results I wanted to get. If you want my design and sql code just let me know and I'll post it. I'm still working on this project though. I'm working on random selections and php code to insert into the database and html/php code to output to a website. I'm also working on a way to pull data from a few sites so I won't have to enter in all the details for each movie.

Thanks for the help. I appreciate it and I'll check out the information you all suggested or gave.

[JOSheaIV]

I konw this is probably considered solved, but I would like to point out the original question never got answered.

The reason you are probably seeing duplicate results is because you are doing an Inner Join. This usually happens because you join two tables, and you don't fully utiliize the PKs of the tables, which can produce duplicate results.

If you really want only one table to be dominate and bring over other snippets from other tables, but not all the data, or you don't care for exact records (which can cause duplicity), you should look at using Left and Right Joins.