我有以下表结构,并在每个表中包含了主键和外键:
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的记录,我原以为内连接只会返回真实存在的结果,而不是所有结果。 更新: 当前数据如下:
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