mysql - 检查行是否被另一个表使用

Question

我有一个表emails_accounts，其结构如下:

╔════╦═════════════════════╦══════════════╗
║ id ║        email        ║ description  ║
╠════╬═════════════════════╬══════════════╣
║  1 ║ test@gmail.com      ║ Lorem        ║
║  2 ║ example@example.com ║ Ipsum        ║
║  3 ║ test@example.com    ║ Dolor        ║
║  4 ║ retail@example.com  ║ Sit Amet     ║
╚════╩═════════════════════╩══════════════╝

每封电子邮件都是独一无二的。

第二个表 email_templates 的结构与此类似:

╔════╦══════════════════╦════════════╦══════════╗
║ id ║ email_title      ║ email_from ║ email_to ║
╠════╬══════════════════╬════════════╬══════════╣
║ 1  ║ Test title       ║ 1          ║ 3        ║
║ 2  ║ Second title     ║ 2          ║ 3        ║
║ 3  ║ Some title       ║ 1          ║ 1        ║
╚════╩══════════════════╩════════════╩══════════╝

email_to 和 email_from 可以不同，但​​也可以相同(如示例所示)。

我想要实现的是创建一个 SQL 查询，该查询为我提供 emails_accounts 表中的 ALL 帐户，但还有其他信息 - 每个帐户在一个帐户中使用了多少表 email_templates(它需要检查 email_from 和 email_to)。

我知道这不应该太难，但到目前为止我没能得到正确的结果。我现在的代码是:

SELECT acc.* , COUNT( temp.id ) 
FROM emails_accounts acc
LEFT JOIN email_templates temp ON acc.id = temp.email_from
GROUP BY acc.email

但我想同时计算 email_from 和 email_to。

我也试过这个:

SELECT acc . * , COUNT( temp.id ) + COUNT( temp2.id ) AS count
FROM emails_accounts acc
LEFT JOIN email_templates temp ON acc.id = temp.email_from
LEFT JOIN email_templates temp2 ON acc.id = temp2.email_to
GROUP BY acc.email

但是它给出的结果太多了。

---

编辑:我有 created a fiddle包含正确答案 - 感谢 jaczes .

Answer 1

那怎么样？

SELECT ea.*,efrom, eto, ifnull(efrom,0)+ifnull(eto,0) as count 
from emails_accounts ea 
LEFT JOIN 
(select email_from,count(email_from) as efrom 
  FROM email_templates group by email_from)
as e_from on ea.id=email_from
LEFT JOIN 
(select email_to, count(email_to) as eto 
  FROM email_templates group by email_to)
as e_to on ea.id=email_to