MySQL:连接查找非匹配字段

Question

我有两张桌子。

客户及语音信箱

Customer有两个与这个问题相关的列，ID & Voicemail。

Voicemail有两个与这个问题相关的列，ID & Customer。

猜测Customer.ID与Voicemail.Customer有关，Customer.Voicemail与Voicemail.ID无关。

我需要得到Customer.Voicemail == Voicemail.ID where CustomerID != Voicemail.Customer的结果。

我不需要在Customer.Voicemail == Voicemail.ID和CustomerID == Voicemail.Customer中返回结果，因为这些结果应该是正确的。

我尝试过使用内部连接，但我可以在其中使用&&吗？当我试图使用连接时，我的心理形象总是变得模糊。

架构以帮助：

Customer
+---+---------+
|ID |Voicemail|
+---+---------+

Voicemail
+---+---------+
|ID | Customer|
+---+---------+ 

Answer 1

从您的模式中猜测一点，看起来您想要这个查询。

SELECT v.ID AS Voicemail_ID,
       c.ID AS Customer_ID, 
       c.some_other_field, 
       v.some_other_field
  FROM Voicemail AS v
 INNER JOIN Customer AS c ON v.ID = c.Voicemail
 WHERE c.ID <> v.Customer

这将为每个语音信箱行引入所有相关的客户行，然后丢弃客户已经指向语音邮件的行。

提示:对表和列使用相同的名称可能会让人感到困惑。如果我是你，我会重做这样的列名

Customer:   Customer_ID (pk), Voicemail_ID, other columns

Voicemail:  Voicemail_ID (pk), Customer_ID, other columns

然后，您可以像这样重写查询：

SELECT v.Voicemail_ID,
       c.Customer_ID,
       c.some_other_field,
       v.some_other_field
  FROM Voicemail AS v
 INNER JOIN Customer AS c USING(Voicemail_ID)
 WHERE c.Customer_ID <> v.Customer_ID

对于下一个在您的项目中工作的人来说，这可能更容易理解。

Answer 2

我认为你可以这么做：

  INNER JOIN Voicemail ON Customer.Voicemail = Vocemail.id AND Customer.id != Voicemail.Customer

希望能帮上忙！