加入两个表不起作用

问题描述

我有两张桌子.表1和表2.

I have 2 tables. Table1 and Table2.

表1数据:

 Id   org_id  start_date    end_date     month
'1', '46', '2015-01-01', '2015-01-31', 'January'
'2', '46', '2015-02-01', '2015-02-28', 'February'
'3', '46', '2015-03-01', '2015-03-31', 'March'
'4', '46', '2015-04-01', '2015-04-30', 'April'
'5', '46', '2015-05-01', '2015-05-31', 'May'
'6', '46', '2015-06-01', '2015-06-30', 'June'
'7', '46', '2015-07-01', '2015-07-31', 'July'
'8', '46', '2015-08-01', '2015-08-31', 'August'
'9', '46', '2015-09-01', '2015-09-30', 'September'
'10', '46', '2015-10-01', '2015-10-31', 'October'
'11', '46', '2015-11-01', '2015-11-30', 'November'
'12', '46', '2015-12-01', '2015-12-31', 'December'

表2数据:

Id   org_id   from_date  emp_id
'48', '46', '2015-06-09'   1
'49', '46', '2015-06-09'   1
'50', '46', '2015-06-01'   2
'51', '46', '2015-05-20'   1
'56', '46', '2015-07-07'   2

这是我的查询:

select t1.month,count(t2.emp_id) as count
from Table1 t1
left outer join  Table2 t2 on t2.from_date between t1.start_date and t1.end_date
where t2.org_id=46 group by t1.month

我得到的输出是:

month   count
'July', '1'
'June', '3'
'May', '1'

我期望的输出是:

 month    count
'January', '0'
'February', '0'
'March', '0'
'April', '0'
'May', '1'
'June', '3'
'July', '1'
'August', '0'
'September', '0'
'October', '0'
'November', '0'
'December', '0'

我使用了左外连接.但是左表中的所有记录都没有被获取.

I have used left outer join. But all records from left table is not getting fetched.

任何帮助！！

推荐答案

你必须把你的 wherestatament 部分放到 on clase:

You have to put the part of your wherestatament to the on clase:

select t1.month,count(t2.emp_id) as count
from Table1 t1
left outer join  Table2 t2 on t2.from_date between t1.start_date and t1.end_date
and t2.org_id=46 group by t1.month

如果你不这样做，你就有一个inner join

If you do not do it you have a inner join

这篇关于加入两个表不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！