mysql - 将子查询转换为连接

Question

鉴于以下架构...

我该如何将以下查询转换为使用联接(与 MySQL 一起使用)？

SELECT submissions.SubmissionID,
(SELECT data.DataContent FROM data WHERE data.DataFieldName =
    (SELECT forms.FormEmailFieldName FROM forms WHERE forms.FormID = submissions.SubmissionFormID)
AND data.DataSubmissionID = submissions.SubmissionID) AS EmailAddress,
(SELECT data.DataContent FROM data WHERE data.DataFieldName =
    (SELECT forms.FormNameFieldName FROM forms WHERE forms.FormID = submissions.SubmissionFormID)
AND data.DataSubmissionID = submissions.SubmissionID) AS UserName
FROM submissions
WHERE submissions.SubmissionFormID = 4

下面是一些示例数据和我想要的结果...

+--------+--------------------+-------------------+
| forms  |                    |                   |
+--------+--------------------+-------------------+
| FormID | FormEmailFieldName | FormNameFieldName |
| 4      | UserEmailAddress   | UserName          |
| 5      | email              | name              |
+--------+--------------------+-------------------+

+--------------+------------------+
| submissions  |                  |
+--------------+------------------+
| SubmissionID | SubmissionFormID |
| 10           | 4                |
| 11           | 5                |
| 12           | 5                |
+--------------+------------------+

+--------+------------------+------------------+------------------+
|  data  |                  |                  |                  |
+--------+------------------+------------------+------------------+
| DataID | DataSubmissionID | DataFieldName    | DataContent      |
| 1      | 10               | UserEmailAddress | user@example.com |
| 2      | 10               | UserName         | Paul D'Otherone  |
| 3      | 11               | email            | bob@bobs.com     |
| 4      | 11               | name             | Bob Bobbington   |
| 5      | 11               | phone            | 01234 5678910    |
| 6      | 11               | country          | UK               |
| 7      | 12               | name             | Sheila Sausages  |
| 8      | 12               | country          | UK               |
+--------+------------------+------------------+------------------+

+--------------------------+------------------+-----------------+
|      DESIRED RESULT      |                  |                 |
+--------------------------+------------------+-----------------+
| submissions.SubmissionID | EmailAddress     | UserName        |
| 10                       | user@example.com | Paul D'Otherone |
| 11                       | bob@bobs.com     | Bob Bobbington  |
| 12                       | NULL             | Sheila Sausages |
+--------------------------+------------------+-----------------+

另请参阅http://sqlfiddle.com/#!2/78dea/1/0

我尝试了内部联接和左联接的各种组合，但无法获得与上述查询格式相同的结果集。我仍在学习如何使用联接，并且发现这很难让我理解。

Answer 1

您可以通过一个到forms的连接和两个到data的连接来完成此操作，每个字段一个。

SELECT s.SubmissionID, de.DataContent as EmailAddress,
       dn.DataContent as UserName
FROM submissions s LEFT JOIN
     forms f
     ON f.FormId = s.SubmissionFormID LEFT JOIN
     data dn
     ON d.DataFieldName = f.FormNameFieldName LEFT JOIN
     data de
     ON d.DataFieldName = f.FormEmailFieldName
WHERE s.SubmissionFormID = 4

如果缺少数据，那么您需要使用LEFT JOIN。这将确保您获得所有行(原始查询所做的)。

我还在查询中添加了表别名。这些使得查询更容易编写和阅读。