如果子查询为空，则会出现 MySQL 问题

Question

我有 5 个表(我只讨论请求中没有的内容)：

• 员工(登录名、机构 ID)
• FreqTest (id， clientEmail， employeeLogin)
• QuestTest (id， clientEmail， employeeLogin)
• SitTest (id， clientEmail， employeeLogin)
• 客户(电子邮件、年龄、性别)

这是我的要求：

SELECT Client.email, Client.age, Client.gender
FROM Client,
(SELECT FreqTest.clientEmail
FROM FreqTest,
(SELECT Employee.login
FROM Employee
WHERE Employee.agencyID = 'anID') EmployeeByAgency
WHERE FreqTest.employeeLogin = EmployeeByAgency.login) Freq,
(SELECT QuestTest.clientEmail
FROM QuestTest,
(SELECT Employee.login
FROM Employee
WHERE Employee.agencyID = 'anID') EmployeeByAgency
WHERE QuestTest.employeeLogin = EmployeeByAgency.login) Quest,
(SELECT SitTest.clientEmail
FROM SitTest,
(SELECT Employee.login
FROM Employee
WHERE Employee.agencyID = 'anID') EmployeeByAgency
WHERE SitTest.employeeLogin = EmployeeByAgency.login) Sit
WHERE Client.email = Freq.emailClient
OR    Client.email = Quest.emailClient
OR    Client.email = Sit.emailClient
GROUP BY Client.email;

我想要任何在代理机构进行测试的客户的电子邮件。 它几乎在任何情况下都有效，除非没有客户在同一机构进行 3 次测试。

我不得不尝试用英语翻译，如果我的英语不完美，我很抱歉。

所以要清楚(与同一个机构)：

• 如果客户 A有FreqTest 和 QuestTest，客户 B 有3个，客户端C没有，我会收到A和B的电子邮件
• 如果客户端 A 有3 个测试，客户端 B 也有3个测试，而客户端C没有，我仍然会收到A和B的电子邮件
• 如果没有客户有3个不同的测试，无论如何我什么也得不到

我想也许是因为如果任何子查询(Freq、Quest 或 Sit)没有返回任何结果，它将是一个 NULL。

如果我不清楚或翻译所有这些错误，我想再次道歉。

Answer 1

使用 LEFT JOIN 避免空引用

SELECT Client.email, Client.age, Client.gender
FROM Client
LEFT JOIN  (SELECT FreqTest.clientEmail
FROM FreqTest,
(SELECT Employee.login
FROM Employee
WHERE Employee.agencyID = 'anID') EmployeeByAgency
WHERE FreqTest.employeeLogin = EmployeeByAgency.login) Freq
ON Client.email = Freq.emailClient
LEFT JOIN  (SELECT QuestTest.clientEmail
FROM QuestTest,
(SELECT Employee.login
FROM Employee
WHERE Employee.agencyID = 'anID') EmployeeByAgency
WHERE QuestTest.employeeLogin = EmployeeByAgency.login) Quest
ON Client.email = Quest.emailClient
LEFT JOIN  (SELECT SitTest.clientEmail
FROM SitTest,
(SELECT Employee.login
FROM Employee
WHERE Employee.agencyID = 'anID') EmployeeByAgency
WHERE SitTest.employeeLogin = EmployeeByAgency.login) Sit
ON  Client.email = Sit.emailClient
WHERE Freq.emailClient is NOT NULL
OR    Quest.emailClient IS NOT NULL
OR    Sit.emailClient IS NOT NULL
GROUP BY Client.email;