Need to return 0 of not present

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need to return 0 of not present

Author

Topic

MBeal
Posting Yak Master

110 Posts

Posted - 2002-08-12 : 13:59:44

I have two tables (A) = Source Code and (B) = Stay. There is a one to many relationship between tables A and B. I want to return every source code from table A and the count (or zero if no records are present) in the column "Reservations"

My current selection looks kind of like this...

SELECT A.Code, Coalesce(Count(B.Stayid),0) as "Reservations"
FROM SourceCode A Left Outer join STAY B
ON A.Code = B.Code
WHERE B.ENTDATE between '08/01/2002' and '08/31/2002'
GROUP BY A.Code

I am only getting the 13 source codes that were used during that time frame -- I am not getting the zeros for all of the other source codes. I suspect it is in the type of join I am using.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-12 : 14:04:23

If you move your ENTDATE condition into the FROM clause, it should work. By leaving it out there in the WHERE clause you are essentially calling for an INNER JOIN.

Jay White
{0}

MBeal
Posting Yak Master

110 Posts

Posted - 2002-08-12 : 14:11:30

Could you please give me an example of how I could move the condition into the FROM Clause?

MBeal

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-12 : 14:46:35

example...


use northwind
go

select
    c.CompanyName,
    count(o.OrderID) as [Number of Orders]
from
    dbo.Customers c
    left join dbo.Orders o
        on c.CustomerID = o.CustomerID
where
    o.Orderdate between '1/1/1998' and '1/1/1999'
group by
    c.companyname
having
    count(o.OrderID) = 0

select
    c.CompanyName,
    count(o.OrderID) as [Number of Orders]
from
    dbo.Customers c
    left join dbo.Orders o
        on (c.CustomerID = o.CustomerID and
            o.Orderdate between '1/1/1998' and '1/1/1999')
group by
    c.CompanyName
having
    count(o.OrderID) = 0

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources