mysql - 如何在有限制的 2 个表之间创建选择连接

Question

英语不是我的母语，所以我提前为错误道歉......

嗯，我有 3 个 MySQL 表:

users:
- Id    INT 11
- Name  VARCHAR 30

calls:
- Id         INT 11
- UserId     INT 11
- DateBegin  DATETIME
- DateEnd    DATETIME
- Phone      VARCHAR 30

contacts:
- Id      INT 11
- UserId  INT 11
- Name    VARCHAR 40
- Phone1  VARCHAR 30
- Phone2  VARCHAR 30

Relationship:
1 user -> many calls (users.Id - calls.UserId)
1 user -> many contacts (users.Id - contacts.UserId)

我需要列出来自特定用户的所有电话，并通过电话号码将其与他们的联系人合并。这是我到目前为止得到的:

SELECT
    calls.Id,
    calls.DateBegin,
    calls.DateEnd,
    calls.Phone,
    contacts.Name
FROM
    calls LEFT JOIN contacts ON
        RIGHT(calls.Number, 8) = RIGHT(contacts.Phone1, 8) OR
        RIGHT(calls.Number, 8) = RIGHT(contacts.Phone2, 8)
WHERE
    calls.UserId = 1 AND contacts.UserId = 1 
GROUP BY
    calls.Id
ORDER BY
    calls.DateBegin DESC;

这段代码有两个问题我无法解决:

1. 它仅列出与联系人相关的调用。如果我删除 WHERE 子句中的“AND contacts.UserId = 1”，它会起作用，但来自其他用户的联系人在连接中是相关的；

2. 有时电话号码是一个空字符串，所以它首先联系空号码并创建连接。我需要它在 calls.phone 为空时忽略联系人。

拜托，一些SQL专家可以帮助我吗？谢谢!

Answer 1

第二个表的条件应该在on子句中:

SELECT c.Id, c.DateBegin, c.DateEnd, c.Phone, MAX(co.Name) as Name
FROM calls c LEFT JOIN
     contacts co
     ON (RIGHT(c.Number, 8) = RIGHT(co.Phone1, 8) OR
         RIGHT(c.Number, 8) = RIGHT(co.Phone2, 8)
        ) AND co.UserId = 1 
WHERE c.UserId = 1
GROUP BY c.Id
ORDER BY c.DateBegin DESC;