Mysql 從多個表中選擇

我有以下表格：

-- Table structure for table `Operator`
--

CREATE TABLE Operator (
 OperatorID int(11) NOT NULL,
 Name varchar(25) NOT NULL,
 Street varchar(25) NOT NULL,
 Town varchar(25) NOT NULL,
 Postcode varchar(25) NOT NULL,
 Email varchar(35) NOT NULL,
 Phone varchar(20) NOT NULL,
 PRIMARY KEY (OperatorID)
) 

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

--
-- Table structure for table `Route`
--

CREATE TABLE Route (
 RouteNumber varchar(5) NOT NULL,
 Frequency int(11) NOT NULL,
 Start varchar(5) NOT NULL,
 Destination varchar(5) NOT NULL,
 PRIMARY KEY (RouteNumber)
) 

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

--
-- Table structure for table `RouteOperators`
--

CREATE TABLE RouteOperators (
 RouteNumber varchar(5) NOT NULL,
 OperatorID int(11) NOT NULL,
 Proportion int(11) NOT NULL,
 PRIMARY KEY (RouteNumber, OperatorID)
) 

我正在嘗試使用以下查詢檢索服務於巴士站 7628（即開始或目的地 = 7628）的運營商的電話號碼和電子郵件：

SELECT Phone,Email FROM Operator, Route, RouteOperators 
WHERE (Route.Start = 7628 OR Route.Destination = 7628) 
AND RouteOperators.RouteNumber = Route.RouteNumber 
AND Operator.OperatorID = RouteOperators.RouteNumber

該查詢不返回任何內容，儘管也沒有錯誤。誰能看到我做錯了什麼？

你可能是說AND Operator.OperatorID = RouteOperators.OperatorID

我建議使用JOIN...ON (...)語法，它使查詢更具可讀性。

SELECT o.Phone,o.Email 
FROM Route r 
INNER JOIN RouteOperators ro 
   ON r.RouteNumber = ro.RouteNumber 
INNER JOIN Operator o 
   ON ro.OperatorID = o.OperatorID 
WHERE Route.Start = 7628 
OR Route.Destination = 7628

您可能還想考慮引入外鍵約束。

代替

AND Operator.OperatorID = RouteOperators.RouteNumber

你應該加入

AND Operator.OperatorID = RouteOperators.OperatorID

學會使用JOIN ... USING(...)。你也會得到很多結果行，除非你GROUP BY.

引用自：https://dba.stackexchange.com/questions/165845