Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Apr 2012
    Posts
    14
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Joining 3 tables

    I have three tables:

    Reviews
    Code:
    CREATE TABLE `reviews` (
     `ID` int(11) NOT NULL AUTO_INCREMENT,
     `GameID` varchar(80) NOT NULL,
     `MemberID` int(11) NOT NULL,
     `Enviroment` double NOT NULL,
     `Enviro-Review` text NOT NULL,
     `Controls` double NOT NULL,
     `Controls-Review` text NOT NULL,
     `Gameplay` double NOT NULL,
     `Gameplay-Review` text NOT NULL,
     `Entertainment` double NOT NULL,
     `Entertain-Review` text NOT NULL,
     `Replayability` double NOT NULL,
     `Replay-Review` text NOT NULL,
     `Date` date NOT NULL,
     PRIMARY KEY (`ID`),
     UNIQUE KEY `ID` (`ID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1
    Members
    Code:
    CREATE TABLE `members` (
     `ID` int(11) NOT NULL AUTO_INCREMENT,
     `Name` varchar(80) NOT NULL,
     `Md5` text NOT NULL,
     `Email` varchar(80) NOT NULL,
     `Admin` int(1) NOT NULL DEFAULT '0',
     PRIMARY KEY (`ID`),
     UNIQUE KEY `ID` (`ID`),
     UNIQUE KEY `Name` (`Name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
    Games
    Code:
    CREATE TABLE `games` (
     `ID` int(11) NOT NULL AUTO_INCREMENT,
     `Name` varchar(80) NOT NULL,
     `Image` varchar(80) NOT NULL,
     `Nintendo Wii` varchar(80) NOT NULL,
     `Nintendo Wii U` varchar(80) NOT NULL,
     `Nintendo DS` varchar(80) NOT NULL,
     `Nintendo 3DS` varchar(80) NOT NULL,
     `PC` varchar(80) NOT NULL,
     `PSP` varchar(80) NOT NULL,
     `Xbox` varchar(80) NOT NULL,
     `Xbox 360` varchar(80) NOT NULL,
     `Playstation Vita` varchar(80) NOT NULL,
     `Playstation 4` varchar(80) NOT NULL,
     `Playstation 3` varchar(80) NOT NULL,
     `Playstation 2` varchar(80) NOT NULL,
     `Playstation` varchar(80) NOT NULL,
     `Developer` varchar(80) NOT NULL,
     `Publisher` varchar(80) NOT NULL,
     `Genre` varchar(80) NOT NULL,
     `Release Date` date NOT NULL,
     `ESRB` varchar(80) NOT NULL,
     PRIMARY KEY (`ID`),
     UNIQUE KEY `ID` (`ID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1
    I want all of the info from reviews, the members's Name and the games Image.

    How would I go about joining all 3 tables like this?

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    This belongs in the mysql forum, so I'll move it there.
    Simple join:
    Code:
    SELECT r.*, m.Name, g.Image
    FROM reviews r
    INNER JOIN members m ON m.ID = r.MemberID
    INNER JOIN games g ON g.ID = r.GameID
    Change r.* to each of the fields you want to select. Avoid wildcarding in your queries.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    But there's a serious error in the table design.

    In the reviews table, you have
    Code:
     `GameID` varchar(80) NOT NULL,
    but in the games table you have
    Code:
      `ID` int(11) NOT NULL AUTO_INCREMENT,
    Those two fields SHOULD MATCH IN TYPE!

    That is, you should have
    Code:
     `GameID` INT NOT NULL,
    The code will probably work as is, but you open yourself up for database errors *AND* you cause MySQL to convert data types on every join. Ugly.

    Further, in the all three tables you have
    Code:
     PRIMARY KEY (`ID`),
     UNIQUE KEY `ID` (`ID`)
    and that's just plain nutso. A primary key *IS* a UNIQUE key, *BY DEFINITION*. So you have added the overhead of an extra UNIQUE KEY for no reason whatsoever!

    And, finally, you are not properly enforcing referential integrity!!!

    Your reviews table has two foreign keys (gameid and memberid), but you don't declare them as such! And so you risk having a corrupted database. You *REALLY* should fix that.

    That is your reviews table should look like:
    Code:
    CREATE TABLE `reviews` (
     `ID` int NOT NULL AUTO_INCREMENT,
     `GameID` int NOT NULL,
     `MemberID` int(11) NOT NULL,
     `Enviroment` double NOT NULL,
     `Enviro-Review` text NOT NULL,
     `Controls` double NOT NULL,
     `Controls-Review` text NOT NULL,
     `Gameplay` double NOT NULL,
     `Gameplay-Review` text NOT NULL,
     `Entertainment` double NOT NULL,
     `Entertain-Review` text NOT NULL,
     `Replayability` double NOT NULL,
     `Replay-Review` text NOT NULL,
     `Date` date NOT NULL,
      PRIMARY KEY (`ID`),
      CONSTRAINT FOREIGN KEY gameid REFERENCES games(id),
      CONSTRAINT FOREIGN KEY memberid REFERENCES members(id)
    ) ENGINE=InnoDB
    Last edited by Old Pedant; 04-25-2013 at 06:29 AM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    New Coder
    Join Date
    Apr 2012
    Posts
    14
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Code:
    CREATE TABLE `reviews` (
     `ID` int NOT NULL AUTO_INCREMENT,
     `GameID` int NOT NULL,
     `MemberID` int(11) NOT NULL,
     `Enviroment` double NOT NULL,
     `Enviro-Review` text NOT NULL,
     `Controls` double NOT NULL,
     `Controls-Review` text NOT NULL,
     `Gameplay` double NOT NULL,
     `Gameplay-Review` text NOT NULL,
     `Entertainment` double NOT NULL,
     `Entertain-Review` text NOT NULL,
     `Replayability` double NOT NULL,
     `Replay-Review` text NOT NULL,
     `Date` date NOT NULL,
      PRIMARY KEY (`ID`),
      CONSTRAINT FOREIGN KEY gameid REFERENCES games(id),
      CONSTRAINT FOREIGN KEY memberid REFERENCES members(id)
    ) ENGINE=InnoDB
    Returns error:
    Code:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REFERENCES games(id), CONSTRAINT FOREIGN KEY memberid REFERENCES members(id) ' at line 17

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,537
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    SORRY! Comes from typing code into these silly little textarea windows.

    Should have parens around the foreign key column. Thus:
    Code:
      CONSTRAINT FOREIGN KEY (gameid) REFERENCES games(id),
      CONSTRAINT FOREIGN KEY (memberid) REFERENCES members(id)
    Sloppiness on my part, though. Mea maxima culpa.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •