...

View Full Version : Joining 3 tables



EffakT
04-25-2013, 03:09 AM
I have three tables:

Reviews

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

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

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?

Fou-Lu
04-25-2013, 05:22 AM
This belongs in the mysql forum, so I'll move it there.
Simple join:


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.

Old Pedant
04-25-2013, 07:26 AM
But there's a serious error in the table design.

In the reviews table, you have

`GameID` varchar(80) NOT NULL,
but in the games table you have

`ID` int(11) NOT NULL AUTO_INCREMENT,

Those two fields SHOULD MATCH IN TYPE!

That is, you should have

`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

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:


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

EffakT
04-25-2013, 10:55 AM
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:

#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

Old Pedant
04-25-2013, 09:11 PM
SORRY! Comes from typing code into these silly little textarea windows.

Should have parens around the foreign key column. Thus:


CONSTRAINT FOREIGN KEY (gameid) REFERENCES games(id),
CONSTRAINT FOREIGN KEY (memberid) REFERENCES members(id)

Sloppiness on my part, though. Mea maxima culpa.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum