Martin Dew wrote:

>
> select
> count(*), o.Name, rt.description
> from
> Log l
> Join Patient p on (l.urn = p.log_urn)
> left outer Join Result_types rt on (p.cons_type = rt.code)
> left outer Join Organisation o on (p.surgery = o.code)
> Where
> (l.taken_at >= '01 Apr 2005 00:00:00'
> and l.Taken_at <= '05 apr 2005 00:00:00')
> and
> p.surgery in ('MAY','GREEN','HEATH','HILLV','PIR','STJOH',
> 'SHEER', 'SOUTH', 'PARIS', 'SUNNY', 'YORK1',
> 'COLL', 'WEST1', 'WEST2', 'CHOB')
> Group By
> o.Name, rt.description
>

One trick that helps in outer joins is to break the rules about what
goes in the ON clause and what goes in the WHERE clause. Putting the
conditions for the inner join terms in the ON clause for the join rather
than the WHERE gives the optimizer a better chance to get the right
answer in V1.5.

Try this:


select
count(*), o.Name, rt.description
from
Log l
Join Patient p on (l.urn = p.log_urn
and (l.taken_at >= '01 Apr 2005 00:00:00'
and l.Taken_at <= '05 apr 2005 00:00:00')
and p.surgery in
('MAY','GREEN','HEATH','HILLV','PIR','STJOH',
'SHEER', 'SOUTH', 'PARIS', 'SUNNY', 'YORK1',
'COLL', 'WEST1', 'WEST2', 'CHOB'))
left outer Join Result_types rt on (p.cons_type = rt.code)
left outer Join Organisation o on (p.surgery = o.code)
Group By
o.Name, rt.description


Good luck

Ann