场景 - 具有参赛者类别的帆船赛事。
我是一名志愿者,我正在努力为每个参赛者获得一个结果表,列出他们对每个类别的选择(如果没有选择,则为 NULL)。请帮我!
参赛者 2097 为所有 3 个类别设置了值,
参赛者 2099 只有两个类别(没有选择船员)
但我需要一行为船员设置 NULL 值。
我需要的输出行如下所示
(即必须包括带有 ecnName = 'Crew'、ecyEntry='2099' 和 eciValue = 'NULL' 的最后一行)
而我的查询不这样做!
+---------+----------+----------+
| ecnName | eciValue | ecyEntry |
+---------+----------+----------+
| Fleet | Gold | 2097 |
| Helm | Male | 2097 |
| Crew | Male | 2097 |
| Fleet | Silver | 2099 |
| Helm | Male | 2099 |
| Crew | NULL | 2099 |
+---------+----------+----------+
我尝试了 LEFT/RIGHT 连接的各种组合,
但没有得到答案,所以非常感谢任何指导!
我的查询在下面,以及源表。
我当前的查询:
[racescoring]>
SELECT *
FROM `tblcategories`
JOIN `tblcategoryitems`
ON eciCategory = ecnID
LEFT JOIN tblentrycategories
ON ecyItem = eciID
WHERE ecnEvent = 57
ORDER BY ecyEntry, ecyItem;
给出的结果包括根本没有参赛者选择的行,并且对于参赛者 2099 Crew 来说不是空的,所以不是我需要的!
+-------+----------+----------+---------+-------+-------------+----------+----------+---------+
| ecnID | ecnEvent | ecnOrder | ecnName | eciID | eciCategory | eciValue | ecyEntry | ecyItem |
+-------+----------+----------+---------+-------+-------------+----------+----------+---------+
| 32 | 57 | 3 | Crew | 61 | 32 | Female | NULL | NULL |
| 31 | 57 | 2 | Helm | 59 | 31 | Female | NULL | NULL |
| 29 | 57 | 1 | Fleet | 55 | 29 | Bronze | NULL | NULL |
| 29 | 57 | 1 | Fleet | 53 | 29 | Gold | 2097 | 53 |
| 31 | 57 | 2 | Helm | 58 | 31 | Male | 2097 | 58 |
| 32 | 57 | 3 | Crew | 60 | 32 | Male | 2097 | 60 |
| 29 | 57 | 1 | Fleet | 54 | 29 | Silver | 2099 | 54 |
| 31 | 57 | 2 | Helm | 58 | 31 | Male | 2099 | 58 |
+-------+----------+----------+---------+-------+-------------+----------+----------+---------+
此特定事件 id=57 有 3 个类别。
[racescoring]> SELECT * FROM tblcategories;
+-------+----------+----------+---------+
| ecnID | ecnEvent | ecnOrder | ecnName |
+-------+----------+----------+---------+
| 29 | 57 | 1 | Fleet |
| 31 | 57 | 2 | Helm |
| 32 | 57 | 3 | Crew |
+-------+----------+----------+---------+
每个类别在此表中都有可能的项目值
[racescoring]> SELECT * FROM tblcategoryitems;
+-------+-------------+----------+
| eciID | eciCategory | eciValue |
+-------+-------------+----------+
| 55 | 29 | Bronze |
| 53 | 29 | Gold |
| 54 | 29 | Silver |
| 59 | 31 | Female |
| 58 | 31 | Male |
| 61 | 32 | Female |
| 60 | 32 | Male |
+-------+-------------+----------+
仅针对两名水手使用条目类别进行测试。
[racescoring]> SELECT * FROM tblentrycategories;
+----------+---------+
| ecyEntry | ecyItem |
+----------+---------+
| 2097 | 53 |
| 2097 | 58 |
| 2097 | 60 |
| 2099 | 54 |
| 2099 | 58 |
+----------+---------+