mysql - 内部 JOIN 返回重复行

Question

我有以下表结构，每个表中都包含了主键和外键:

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***
) 

我设置了以下外键:

 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 `);

我的问题是当我运行以下 INNER JOIN 查询时:

 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');

我期望返回两行，因为只有两条 ID 为 43 的记录，如“WHERE”子句中所述。相反，它返回 8 个 ID 为 43 的记录，我认为 INNER Join 只会返回为真的结果，而不是所有结果。

更新

当前数据如下:

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);

当前输出是:

 |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

预期输出是:

|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

你说在 table1 中有两条 id 为 43 的记录。但是这在 table2、table3 ... table5 中被引用。

最后，您将显示与 table1 中 id 为 43 的那两行数据的所有关系。

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

如果您从表 1 中选择 ID = 1 的位置，那么如果您加入表 2，您仍然会得到两行结果。

编辑:

使用问题中的数据更新，选择 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

您在表 5 中有两列称为“其他数据”，但其中一列似乎是表 1 的 FK。如果是这样，使用这个:

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');