Left outer join and NULL rows 2

[graabein]

I'm going crazy. I've been working on this query for two days on/off now and I can't figure it out. Maybe someone here can see what I'm doing wrong?

I have a temp table #Ward consisting of a varchar, the same datatype as used in my main table.

I want this paired up with Operation table to get the number of operations on each ward for a period of time. The key is I want to show every ward even if it has had no operations.

Let's say the Operation table looks like this:

Ward | Hosp | Dept | Date | InTime | OutTime | Type

My current code is:

select
    w.Ward as "Ward",
    count(o.Date) as "Total",
    sum(case o.Type when 0 then 1 else 0 end) as "Type0",
    sum(case when o.Type > 0 then 1 else 0 end) as "Type1",
from
    #Ward w left outer join Operation o on w.Ward = o.Ward
where
    (o.Hosp = 'TR' or o.Hosp is null) and
    (o.Dept = 'KIR' or o.Dept is null) and
    (o.Date >= '2006-10-01' and o.Date < '2006-11-01' or o.Date is null)
group by
    w.Ward
order by 
    w.Ward

When I run the query on a period of time I have operations on the first four wards (1-4) I get those numbers and zeros for the rest of the wards (5-10). This is good.

But when I try it on a different period of time (outside the dates I have operations) I do not get wards 1-4 in my return set!!?? I only get wards 5-10 with zeros... I don't get it.

I would be most grateful for any help.



graabein

 

[gmmastros]

I don't know if this is causing your problem, but it may.

I suggest you change:

(o.Date >= '2006-10-01' and o.Date < '2006-11-01' or o.Date is null)

To

[tt][blue]
([!]([/!]o.Date >= '2006-10-01' and o.Date < '2006-11-01'[!])[/!] or o.Date is null)[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Since your where clause includes the left table (o), it is acting as an inner join. Consider moving the where conditions to the ON conditions, like this...

select
    w.Ward as "Ward",
    count(o.Date) as "Total",
    sum(case o.Type when 0 then 1 else 0 end) as "Type0",
    sum(case when o.Type > 0 then 1 else 0 end) as "Type1",
from
    #Ward w 
    left outer join Operation o 
      on  w.Ward = o.Ward
      and (o.Hosp = 'TR' or o.Hosp is null)
      and (o.Dept = 'KIR' or o.Dept is null)
      and ((o.Date >= '2006-10-01' and o.Date < '2006-11-01') or o.Date is null)
group by
    w.Ward
order by 
    w.Ward

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[graabein]

That was brilliant George. Thanks a lot! The parenthesis around ((A and B) or C) was not necessary but when I moved the where clause to the on condition it worked perfectly.

So the lesson learned is that when a join does not include where clauses on both tables then the clauses should be applied as join conditions. Something like that?

Thanks again!

 

[r937]

george got his left and right tables mixed up, o is the right table in this example

when you have conditions on the right table in a LEFT OUTER JOIN, those conditions should be in the ON clause, and you should not test for nulls

  from #Ward w 
left outer 
  join Operation o 
    on o.Ward = w.Ward
   and o.Hosp = 'TR'
   and o.Dept = 'KIR'
   and o.Date >= '2006-10-01' 
   and o.Date < '2006-11-01'

r937.com | rudy.ca

 

[graabein]

That also works Thanks!