sql - 如何从 2 个连接表中检索数据 - 帐户和别名？

Question

我有 2 个表，帐户和别名，其中包含电子邮件帐户以及应该重定向到谁的内容：

accounts:
  id
  name
  domain_name

aliases:
  id
  source_account_id
  destination_account_id
  status

如何在给定源的情况下返回目标电子邮件？我试过这个：

SELECT concat(ac.name, '@', ac.domain_name) as destination
FROM accounts ac

INNER JOIN aliases as al1 ON al1.source_account_id = ac.id
INNER JOIN aliases as al2 ON al2.destination_account_id = ac.id

WHERE
    concat(ac.name, '@', ac.domain_name) = '%s'
    AND al1.status = 'active' 
    AND al1.id = al2.id;

但它没有按预期工作

标签： sqldatabase

Answer 1

您需要使用该account表两次，如下所示：

SELECT concat(d.name, '@', d.domain_name) as destination
FROM accounts s
INNER JOIN aliases a ON a.source_account_id = s.id
INNER JOIN ACCOUNTS D ON a.destination_account_id = d.id
WHERE concat(s.name, '@', s.domain_name) = '%s'
  AND a.status = 'active' ;