SQL 左联接，其中联接列中缺少数据

Question

我什至不确定这是否可行，但我正在尝试将两个表连接在一起，但没有得到预期的结果。我的查询如下:

SELECT inc.NUMBER as TICKET,
inc.UNIV_NUM,
inc.ASSIGNEE,
work.SUBMIT_DATE
work.TYPE
FROM dbo.HELP_DESK as inc
LEFT JOIN dbo.WORKLOG as work on inc.NUMBER = work.NUMBER
Where inc.ASSIGNEE = 'AB049732'
and work.SUBMIT_DATE = (Select MAX(work2.SUBMIT_DATE) 
                        from dbo.WORKLOG as work2 
                        where work2.NUMBER = work.NUMBER
                        and work2.TYPE = '16000')

我的表格是这样的

公司

+---------+-----------+----------+
| NUMBER  | UNIV_NUM  | ASSIGNEE |
+---------+-----------+----------+
|  100001 |   4321781 | AB049732 |
|  100002 |   4232756 | AB049732 |
|  100003 |   4322534 | AB049732 |
|  100004 |   4328534 | AB049732 |
+---------+-----------+----------+

工作

+--------+------------+-------+
| NUMBER |   DATE     | TYPE  |
+--------+------------+-------+
| 100001 | 23/05/2018 | 16000 |
| 100003 | 22/05/2018 | 16000 |
| 100004 | 22/05/2018 | 16000 |
+--------+------------+-------+

我的预期输出是:

+--------+----------+----------+------------+-------+
| NUMBER | UNIV_NUM | ASSIGNEE |    DATE    | TYPE  |
+--------+----------+----------+------------+-------+
| 100001 |  4321781 | AB049732 | 23/05/2018 | 16000 |
| 100002 |  4232756 | AB049732 | NULL       | NULL  |
| 100003 |  4322534 | AB049732 | 22/05/2018 | 16000 |
| 100004 |  4328534 | AB049732 | 22/05/2018 | 16000 |
+--------+----------+----------+------------+-------+

但我的实际输出是:

+---------+-----------+----------+------------+-------+
| NUMBER  | UNIV_NUM  | ASSIGNEE |   DATE     | TYPE  |
+---------+-----------+----------+------------+-------+
|  100001 |   4321781 | AB049732 | 23/05/2018 | 16000 |
|  100003 |   4322534 | AB049732 | 22/05/2018 | 16000 |
|  100004 |   4328534 | AB049732 | 22/05/2018 | 16000 |
+---------+-----------+----------+------------+-------+

实际上，尽管在 inc 表中，数字 100002 并未显示。是我做错了什么，还是你不能加入不存在的东西？

Answer 1

您的加入条件不好。试试这个:

SELECT inc.NUMBER as TICKET,
inc.UNIV_NUM,
inc.ASSIGNEE,
work.SUBMIT_DATE
work.TYPE
FROM dbo.HELP_DESK as inc
LEFT JOIN dbo.WORKLOG as work on inc.NUMBER = work.NUMBER
and work.SUBMIT_DATE = (Select MAX(work2.SUBMIT_DATE) 
                        from dbo.WORKLOG as work2 
                        where work2.NUMBER = work.NUMBER
                        and work2.TYPE = '16000')
Where inc.ASSIGNEE = 'AB049732'

看出区别了吗？如果您将 work.SUBMIT_DATE = ... 条件放在 Where 子句中(就像您所做的那样)，那么您的连接将成为内部连接。但是你想要一个外部连接。