mysql - 无法在 mysql 中加入 2 个语句

Question

我的数据库 >

CREATE TABLE `tblcompetition` (
  `fldCompID` int(11) NOT NULL,
  `fldDate` date NOT NULL,
  `fldCompName` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dataark for tabell `tblcompetition`
--

INSERT INTO `tblcompetition` (`fldCompID`, `fldDate`, `fldCompName`) VALUES
(1, '2018-12-31', 'Winter Warmer'),
(2, '2019-01-31', 'Fresh New Year'),
(3, '2019-02-28', 'Month of Love'),
(4, '2018-11-30', 'Seaside Scenery');

-- --------------------------------------------------------

--
-- Tabellstruktur for tabell `tblimage`
--

CREATE TABLE `tblimage` (
  `fldImageID` int(11) NOT NULL,
  `fldMemberID` int(11) NOT NULL,
  `fldCatID` int(11) NOT NULL,
  `fldFilePath` varchar(256) NOT NULL,
  `fldName` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dataark for tabell `tblimage`
--

INSERT INTO `tblimage` (`fldImageID`, `fldMemberID`, `fldCatID`, `fldFilePath`, `fldName`) VALUES
(6, 1, 6, 'images/Screen Shot 2019-02-14 at 14.42.07.png', 'Sunrise'),
(7, 1, 6, 'images/Screen Shot 2019-02-14 at 14.42.07.png', 'SunDown'),
(9, 11, 4, 'Imge1', 'Time Goes By'),
(18, 1, 1, 'images/Aloe.jpg', 'Aloe'),
(19, 1, 1, 'images/Aloe.jpg', 'Blur'),
(20, 1, 1, 'images/Aloe.jpg', 'Winter Time in the Country'),
(21, 1, 1, 'images/Aloe.jpg', 'Warmth'),
(22, 11, 4, 'he', 'Tomorrow');

-- --------------------------------------------------------

--
-- Tabellstruktur for tabell `tblmembentcomp`
--

CREATE TABLE `tblmembentcomp` (
  `fldMembEntCompID` int(11) NOT NULL,
  `fldCompID` int(11) NOT NULL,
  `fldMemberID` int(11) NOT NULL,
  `fldResult` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dataark for tabell `tblmembentcomp`
--

INSERT INTO `tblmembentcomp` (`fldMembEntCompID`, `fldCompID`, `fldMemberID`, `fldResult`) VALUES
(1, 2, 11, 11),
(2, 2, 1, 17),
(3, 2, 5, 19),
(4, 2, 3, 7),
(5, 2, 12, 4);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblcompetition`
--
ALTER TABLE `tblcompetition`
  ADD PRIMARY KEY (`fldCompID`);

--
-- Indexes for table `tblimage`
--
ALTER TABLE `tblimage`
  ADD PRIMARY KEY (`fldImageID`),
  ADD KEY `fldMemberID` (`fldMemberID`),
  ADD KEY `fldCatID` (`fldCatID`);

--
-- Indexes for table `tblmembentcomp`
--
ALTER TABLE `tblmembentcomp`
  ADD PRIMARY KEY (`fldMembEntCompID`),
  ADD UNIQUE KEY `fldMemberID` (`fldMemberID`),
  ADD KEY `fldCompID` (`fldCompID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblcompetition`
--
ALTER TABLE `tblcompetition`
  MODIFY `fldCompID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `tblimage`
--
ALTER TABLE `tblimage`
  MODIFY `fldImageID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;

--
-- AUTO_INCREMENT for table `tblmembentcomp`
--
ALTER TABLE `tblmembentcomp`
  MODIFY `fldMembEntCompID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- Begrensninger for dumpede tabeller
--

--
-- Begrensninger for tabell `tblimage`
--
ALTER TABLE `tblimage`
  ADD CONSTRAINT `tblimage_ibfk_1` FOREIGN KEY (`fldMemberID`) REFERENCES `tblmember` (`fldMemberID`),
  ADD CONSTRAINT `tblimage_ibfk_2` FOREIGN KEY (`fldCatID`) REFERENCES `tblcategory` (`fldCatID`);

--
-- Begrensninger for tabell `tblmembentcomp`
--
ALTER TABLE `tblmembentcomp`
  ADD CONSTRAINT `tblmembentcomp_ibfk_1` FOREIGN KEY (`fldCompID`) REFERENCES `tblcompetition` (`fldCompID`),
  ADD CONSTRAINT `tblmembentcomp_ibfk_2` FOREIGN KEY (`fldMemberID`) REFERENCES `tblmember` (`fldMemberID`);

当我这样做的时候>

SELECT tblmembentcomp.fldCompID, tblmembentcomp.fldMemberID, tblmembentcomp.fldResult, tblimage.fldMemberID FROM tblmembentcomp JOIN tblcompetition ON tblmembentcomp.fldCompID=tblcompetition.fldCompID  ORDER BY tblmembentcomp.fldResult DESC LIMIT 3

我得到 19,17,11 是正确的，但是当我添加 > JOIN tblimage ON tblmembentcomp.fldMemberID=tblimage.fldMemberID

或完整的搜索字符串>

 SELECT tblmembentcomp.fldCompID, tblmembentcomp.fldMemberID, tblmembentcomp.fldResult, tblimage.fldMemberID FROM tblmembentcomp JOIN tblcompetition ON tblmembentcomp.fldCompID=tblcompetition.fldCompID JOIN tblimage ON tblmembentcomp.fldMemberID=tblimage.fldMemberID ORDER BY tblmembentcomp.fldResult DESC LIMIT 3

我只得到 17,17,17

标签： mysql