Inner JOIN is returning duplicate rows

Question

I have the following table structure and I have included the primary and foreign keys in each table:

CREATE TABLE `Table1` (
`Table1_ID` int(6) ,
`Table2_FK` int(6) ,
 **Other Fields***
) 

CREATE TABLE `Table2` (
`Table2_ID` int(6) ,
`Table3_FK` int(11),
 **Other Fields***
) 
CREATE TABLE `Table3` (
`Table3_ID` int(6) ,
`Table2_FK` int(11),
 **Other Fields***
) 

CREATE TABLE `Table4` (
`Table4_ID` int(6) ,
`Table3_FK` int(11),
 **Other Fields***
) 

CREATE TABLE `Table5` (
`Table5_ID` int(6) ,
`Table4_FK` int(6),
 **Other Fields***
) 

I have set up the following foreign keys:

 ALTER TABLE `Table5`
 ADD CONSTRAINT `table5_ibfk_4` FOREIGN KEY (`Table4_FK `) REFERENCES   `Table4` (`Table4_ID`);

 ALTER TABLE `Table4`
 ADD CONSTRAINT `table4_ibfk_3` FOREIGN KEY (`Table3_FK `) REFERENCES `Table3` (`Table3_ID`);

 ALTER TABLE `Table1`
ADD CONSTRAINT `table1_ibfk_2` FOREIGN KEY (`Table2_FK `) REFERENCES     `Table2` (`Table2_ID `);

 ALTER TABLE `Table2`
 ADD CONSTRAINT `table2_ibfk_1` FOREIGN KEY (`Table3_FK`) REFERENCES `Table3` (`Table3_ID `);

My problem is when I run the following INNER JOIN query:

 SELECT *
 FROM `Table1 `
 INNER JOIN `Table2` ON `Table1`.`Table2_FK` =`Table2`.`Table2_ID` 
 INNER JOIN `Table3` ON `Table2`.`Table3_FK` = `Table3`.`Table3_ID` 
 INNER JOIN `Table4` ON `Table3`.`Table3_ID` = `Table4`.`Table3_FK ` 
 INNER JOIN `Table5` ON `Table4`.`Table4_ID` = `Table5`.`Table4_FK ` 
 WHERE (`Table1`.`Table1_ID ` ='43');

I am expecting two rows to be returned as there are only two records where the ID is 43, as stated in the 'WHERE' clause. Instead it returns 8 records with an ID of 43, I thought an INNER Join would only return results where it is true instead of all results.

UPDATE

Current Data is below:

INSERT INTO `Table1` (`Table1_ID `, `OtherData`, `Table2_FK `, `OtherData2`, `Date`) VALUES
(42, 1, 1, 'New', '2015-03-10 17:41:50'),
(43, 1, 1, 'New', '2015-03-10 17:44:35'),
(44, 1, 1, 'New', '2015-03-10 17:50:34'),
(45, 1, 1, 'New', '2015-03-10 17:55:20'),
(46, 1, 1, 'New', '2015-03-10 18:10:47');

INSERT INTO `Table2` (`Table2_ID `, `OtherData3`, `OtherData4 `,     `OtherData5`, `OtherData6`) VALUES
(1, 'blahtype', NULL, 1, '2015-03-13 00:00:00');

INSERT INTO `Table3` (`Table3_ID `, `Table2_FK `, `OtherData6`) VALUES
(1, 1, 'blahname');

INSERT INTO `Table4` (`Table4_ID`, `Table3_FK `, `OtherData6`, `OtherData7`,     `OtherData7`) VALUES
(2, 1, 'blahfieldname', 'blahcont', 'blahtype'),
(3, 1, 'blahfieldname2', 'blahcont', 'blahtype');

INSERT INTO `Table5` (`Table5_ID `, `OtherData`, `Table4_FK`, `OtherData`) VALUES
(1, 'test2', 2, 42),
(2, 'test3', 3, 42),
(3, 'Test4', 2, 43),
(4, 'test5', 3, 43),
(5, 'test6', 2, 44),
(6, 'test7', 3, 44),
(9, 'test8', 2, 78),
(10, 'test9',3, 78);

Current Output is:

 |43|1|1|New|2015-03-10 17:44:35|1| blahtype |NULL|1|2015-03-13 00:00:00|1|1| blahname |2|1| blahfieldname | blahcont | blahtype |1|test2|2|42
 |43|1|1|New|2015-03-10 17:44:35|1| blahtype |NULL|1|2015-03-13 00:00:00|1|1| blahname |2|1| blahfieldname | blahcont | blahtype |3|test3|2|43
 |43|1|1|New|2015-03-10 17:44:35|1| blahtype |NULL|1|2015-03-13 00:00:00|1|1| blahname |2|1| blahfieldname | blahcont | blahtype |5|test4|2|44
 |43|1|1|New|2015-03-10 17:44:35|1| blahtype |NULL|1|2015-03-13 00:00:00|1|1| blahname |2|1| blahfieldname | blahcont | blahtype |9|test5|2|78
 |43|1|1|New|2015-03-10 17:44:35|1| blahtype |NULL|1|2015-03-13 00:00:00|1|1| blahname |3|1| blahfieldname2| blahcont | blahtype |2|test6|3|42
 |43|1|1|New|2015-03-10 17:44:35|1| blahtype |NULL|1|2015-03-13 00:00:00|1|1| blahname |3|1| blahfieldname2| blahcont | blahtype |4|test7|3|43
 |43|1|1|New|2015-03-10 17:44:35|1| blahtype |NULL|1|2015-03-13 00:00:00|1|1| blahname |3|1| blahfieldname2| blahcont | blahtype |6|test8|3|44
 |43|1|1|New|2015-03-10 17:44:35|1| blahtype |NULL|1|2015-03-13 00:00:00|1|1| blahname |3|1| blahfieldname2| blahcont | blahtype |10|test9|3|78

Expected output is:

|43|1|1|New|2015-03-10 17:44:35|1| blahtype |NULL|1|2015-03-13 00:00:00|1|1| blahname |2|1| blahfieldname | blahcont | blahtype |3|test3|2|43
|43|1|1|New|2015-03-10 17:44:35|1| blahtype |NULL|1|2015-03-13 00:00:00|1|1| blahname |3|1| blahfieldname2| blahcont | blahtype |4|test7|3|43

Answer 1

You say there are two records where the id is 43 - in table1. But then this is referensed in table2, table3 ... table5.

In the end you display every relation to those two rows of data with id 43 in table1.

table1
ID    name
1     T1-Firstrow
2     T1-Secondrow

table2
ID    FK    name
1     1     T2-Firstrow
2     1     T2-Secondrow
3     2     T2-Thirdrow

If you'd select where ID = 1 from table1, you'd still get two rows as result if you join in table2.

edit:

using the update of data in your question, selecting id 43:

table1 has 1 row matching
table2 has 1 row matching
table3 has 1 row matching
table4 has 2 rows matching
table5 has 8 rows matching

You have two columns called 'otherdata' in table5, but one of them seem to be a FK to table1. If so, use this:

SELECT *
 FROM `Table1 `
 INNER JOIN `Table2` ON `Table1`.`Table2_FK` =`Table2`.`Table2_ID` 
 INNER JOIN `Table3` ON `Table2`.`Table3_FK` = `Table3`.`Table3_ID` 
 INNER JOIN `Table4` ON `Table3`.`Table3_ID` = `Table4`.`Table3_FK` 
 INNER JOIN `Table5` ON `Table4`.`Table4_ID` = `Table5`.`Table4_FK` AND 
                        `Table5`.`OtherDataFK` = `Table1`.`Table1_ID`
 WHERE (`Table1`.`Table1_ID ` ='43');

Answer 2

You are correct that inner join only returns results that are matched on both sides.

However, I don't see any primary keys here. Assuming that you just didn't mention them, and that all ForeignKeys are pointing to PrimaryKeys in the target table, then we have Table2ID as a primary key, Table3 ID as a primary key, Table5 ID as a primary key. You state Table1 only has two records where ID = 43.

I assume Table4 has multiple records with a ForeignKey equal to the ID of the records in Table 3 that have an ID equal to the foreign key of records in Table 2 which have an ID equal to the foreign key in Table 1.

Using the data you just edited in,

Table 1 returns record 2, the one with 43 as the ID. This record joins to Table2 on FK of 1 equal to the one record in Table2. This record joins to Table3 with the one record in Table 3. This record joins to both records in table 4, so now we have 2 records. These two records join to table 5 on table 4 ID = Table 5 FK, each of these two records joins to 4 records in table 5.

This gives you 8 total records in your result set.

~Edit It is 8 total records, you skipping to ID 10 I assumed there were 10 records in table 5. Also your Table 2 is missing it's FK.