sql查询返回null

Question

我使用完全相同的查询从两个表中获取数据。当我不使用c.IsReceived = 0部件时，它将返回日期，但在使用时不会返回任何内容。在RepDailyCollection中有的IsReceived = 0。你认为我做错了什么？这是在2005年的SQL Server。 thnak你！

Select 
    distinct RepDailyInfo.Date 
from 
    RepDailyInfo 
left outer join 
    RepDailyCollection c 
on 
    c.RepDailyCollectionID = RepDailyInfo.RepDailyInfoID 
where 
    RepDailyInfo.RepID = 205 and c.IsReceived = 0 

编辑：

当我使用类似的其他存储过程正常工作。

Select i.Date 
--i.RepDailyInfoID, i.RepID, i.TypeofDayID, i.CommuniTeeID, sum(cast(c.AmountSold as numeric(10,2))) as AmountSold, 
-- sum(cast (c.AmountCollected as numeric(10,2))) as AmountCollected, c.NewCompanyName,c.PaymentMethod, 
-- c.TypeofCreditCard, c.CheckNumber, c.Invoice, c.Spots, TypeofDay.TypeofDay, CommuniTee.City, CommuniTee.State, 
-- CommuniTee.year, SalesRep_Info.FirstName, SalesRep_Info.LastName 
from RepDailyInfo i 
left outer join RepDailyCollection c on i.RepDailyInfoID = c.RepDailyInfoID 
left outer join TypeOfDay on TypeOfDay.TypeofDayID = i.TypeofDayID 
left outer join SalesRep_Info on SalesRep_Info.RepID = i.RepID 
left outer join CommuniTee on CommuniTee.CommuniTeeID = i.CommuniTeeID 
where i.RepID = 205 and c.IsReceived = 0 
group by i.RepDailyInfoID, i.Date, i.TypeofDayID, i.CommuniTeeID, SalesRep_Info.FirstName, TypeofDay.TypeofDay, 
CommuniTee.City, CommuniTee.State, CommuniTee.year, SalesRep_Info.FirstName, 
SalesRep_Info.LastName, i.RepID, c.NewCompanyName, c.PaymentMethod, c.TypeofCreditCard, c.CheckNumber, c.Invoice, c.Spots 
order by SalesRep_Info.FirstName desc 

Answer 1

这里有很多错误。

在第一个查询您有：

Select 
    distinct RepDailyInfo.Date 
from 
    RepDailyInfo 
left outer join 
    RepDailyCollection c 
on 
    c.RepDailyCollectionID = RepDailyInfo.RepDailyInfoID 
where 
    RepDailyInfo.RepID = 205 and c.IsReceived = 0 

即使你说的“左外连接”，由于where子句你不妨说内连接。

无论如何，这个查询与第二个不匹配。在第二个查询中，您正在将RepDailyCollection加入RepDailyInfo的RepDailyInfoID。在第一个查询中，您将加入完全不同的列。

Answer 2

如果你有 “LEFT JOIN ... ON ... = RepDailyInfo.RepDailyInfoID”，应该说实际上是 “LEFT JOIN ... ON ... = RepDailyInfo.RepDailyCollectionID”？

Answer 3

难道它是一个varchar/char字段？

c.IsReceived = '0' 

Answer 4

有可能在RepDailyCollection记录与IsReceived = 0和RepID = 205，但如果没有在RepDailyInfo任何记录与205 REPID则没有记录会因为你的where子句中返回。

根据您的陈述与RepDailyCollection

有行IsReceived = 0 REPID = 205

这听起来像你的where子句应该是：

c.RepID = 205 and c.IsReceived = 0 

代替的

RepDailyInfo.RepID = 205 and c.IsReceived = 0 

Answer 5

为您的测试..你可以做下面的查询..

Select distinct c.IsReceived from RepDailyInfo 
left outer join RepDailyCollection c on c.RepDailyCollectionID = RepDailyInfo.RepDailyInfoID 
where RepDailyInfo.RepID = 205 and c.IsReceived = 0 

如果你能看到0，则此栏必须是VARCHAR2数据类型..

然后用下面的查询\

Select distinct RepDailyInfo.Date from RepDailyInfo 
left outer join RepDailyCollection c on c.RepDailyCollectionID = RepDailyInfo.RepDailyInfoID 
where RepDailyInfo.RepID = 205 and c.IsReceived = '0'