Mysql Movie Database

Question

I'm creating a movie database and I think I have finished designing it, but my results are not coming back as I would like them too. I only have 2 movies details in the database right now. I'm trying to get it to stop displaying duplicate information.

Can you take a look and give feedback?

Thanks for the help in advance.

/*
 Navicat MySQL Data Transfer

 Source Server         : localhost
 Source Server Version : 50525
 Source Host           : localhost
 Source Database       : MovieDB

 Target Server Version : 50525
 File Encoding         : utf-8

 Date: 09/12/2013 22:06:21 PM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `FORMAT`
-- ----------------------------
DROP TABLE IF EXISTS `FORMAT`;
CREATE TABLE `FORMAT` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `Formats` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `FORMAT_ID`
-- ----------------------------
DROP TABLE IF EXISTS `FORMAT_ID`;
CREATE TABLE `FORMAT_ID` (
  `MovieID` int(11) NOT NULL DEFAULT '0',
  `FormatID` int(11) NOT NULL DEFAULT '0',
  `Num_Discs` int(128) DEFAULT NULL,
  `Locations` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`MovieID`,`FormatID`),
  KEY `FormatID` (`FormatID`),
  CONSTRAINT `FORMAT_ID_ibfk_1` FOREIGN KEY (`MovieID`) REFERENCES `MOVIE` (`ID`),
  CONSTRAINT `FORMAT_ID_ibfk_2` FOREIGN KEY (`FormatID`) REFERENCES `FORMAT` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `GENRE`
-- ----------------------------
DROP TABLE IF EXISTS `GENRE`;
CREATE TABLE `GENRE` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Genres` varchar(50) NOT NULL DEFAULT '',
  `Descriptions` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `GENRE_ID`
-- ----------------------------
DROP TABLE IF EXISTS `GENRE_ID`;
CREATE TABLE `GENRE_ID` (
  `MovieID` int(11) NOT NULL DEFAULT '0',
  `GenreID` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`MovieID`,`GenreID`),
  KEY `GenreID` (`GenreID`),
  CONSTRAINT `GENRE_ID_ibfk_2` FOREIGN KEY (`GenreID`) REFERENCES `GENRE` (`ID`),
  CONSTRAINT `GENRE_ID_ibfk_1` FOREIGN KEY (`MovieID`) REFERENCES `MOVIE` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `MOVIE`
-- ----------------------------
DROP TABLE IF EXISTS `MOVIE`;
CREATE TABLE `MOVIE` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Titles` varchar(128) NOT NULL DEFAULT '',
  `Alt_Titles` varchar(128) DEFAULT NULL,
  `Types` varchar(10) NOT NULL DEFAULT '',
  `Synopsis` varchar(128) DEFAULT NULL,
  `Images` varchar(128) DEFAULT NULL,
  `Num_Eps` int(10) DEFAULT '0',
  `Catagories` varchar(50) NOT NULL DEFAULT '',
  `Duration` time DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Table structure for `RENT`
-- ----------------------------
DROP TABLE IF EXISTS `RENT`;
CREATE TABLE `RENT` (
  `MovieID` int(11) NOT NULL DEFAULT '0',
  `Rents` varchar(3) NOT NULL DEFAULT '',
  `Who` varchar(128) NOT NULL DEFAULT '',
  `Note` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`MovieID`,`Rents`,`Who`),
  CONSTRAINT `RENT_ibfk_1` FOREIGN KEY (`MovieID`) REFERENCES `MOVIE` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

sql:

select distinct Titles, Types, Num_Eps, Catagories, Duration, Formats, Num_Discs, Genres
from MOVIE, FORMAT, FORMAT_ID, GENRE, GENRE_ID
where MOVIE.ID=GENRE_ID.MovieID
AND GENRE_ID.GenreID=GENRE.ID
and MOVIE.ID=FORMAT_ID.MovieID
AND FORMAT_ID.FormatID=FORMAT.ID
order by Titles;

this is my results:

Titles,Types,Num_Eps,Catagories,Duration,Formats,Num_Discs,Genres
Ghost in the Shell,Movie,1,Anime,01:23:00,DVD,1,Mecha
Ghost in the Shell,Movie,1,Anime,01:23:00,DVD,1,Police
Ghost in the Shell,Movie,1,Anime,01:23:00,DVD,1,Psychological
Ghost in the Shell,Movie,1,Anime,01:23:00,DVD,1,Sci-Fi
Ghost in the Shell,Movie,1,Anime,01:23:00,DVD,1,Cyberpunk
Ghost in the Shell,Movie,1,Anime,01:23:00,Bluray,1,Mecha
Ghost in the Shell,Movie,1,Anime,01:23:00,Bluray,1,Police
Ghost in the Shell,Movie,1,Anime,01:23:00,Bluray,1,Psychological
Ghost in the Shell,Movie,1,Anime,01:23:00,Bluray,1,Sci-Fi
Ghost in the Shell,Movie,1,Anime,01:23:00,Bluray,1,Cyberpunk
Summer Wars,Movie,1,Anime,01:54:00,DVD,1,Comedy
Summer Wars,Movie,1,Anime,01:54:00,DVD,1,Sci-Fi
Summer Wars,Movie,1,Anime,01:54:00,HD,,Comedy
Summer Wars,Movie,1,Anime,01:54:00,HD,,Sci-Fi

Answer 1

Nobody is probably looking at this thread any more, but I wanted to let future views and the people who helped me that I found what I was looking for in the question.

select DISTINCT m.Title, 
            group_concat(DISTINCT g.Genre) as Genres, 
            group_concat(DISTINCT f.Format) as Formats
from MOVIES m 
left join MOVIEGENRES mg on m.Code=mg.MovieCode
left join GENRES g on mg.GenCode=g.Code
left join MOVIEFORMATS mf on m.Code=mf.MovieCode
left join FORMATS f on mf.FormCode=f.Code
group by m.Code;

Answer 2

Update: Thanks to Vulcronos I got genres and formats to list out right if I do them separate by using

Genre:

select distinct Titles, Types, Num_Eps, Catagories, Duration, Genres 
FROM MOVIE JOIN GENRE_ID ON MOVIE.ID=GENRE_ID.MovieID, GENRE 
where GENRE_ID.GENREID=GENRE.ID 
order by Titles;

format:

select distinct Titles, Types, Num_Eps, Catagories, Duration, Formats, Num_Discs, Locations 
FROM MOVIE JOIN FORMAT_ID ON MOVIE.ID=FORMAT_ID.MovieID, FORMAT 
where FORMAT_ID.FORMATID=FORMAT.ID 
order by Titles;

Is there any way to combine the two?

So I would get something like this:

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

Where it would list the genres once and the formats once. Of course with multiple genres the format will be repeated unless there is a way to fix that, but where it will at least show all the formats once along with genres once.

Thanks for the help.

Answer 3

A few points. I would use full join syntax,

FROM MOVIE JOIN GENRE ON MOVIE.ID=GENRE_ID.MovieID

instead of the where clauses you have now.

Then you might want to try GROUP_CONCAT(expr) to concatenate your Genres into one line, ex. Mecha, Police, Psychological, etc...