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.