Re: multiple outer joins problem

From: Peter Nilsson <airia_at_acay.com.au>
Date: Sun, 12 Sep 2010 17:57:51 -0700 (PDT)
Message-ID: <da62cef4-bec7-43d4-99e8-ff04b376f6be_at_x20g2000pro.googlegroups.com>

"dn.p..._at_gmail.com" <dn.p..._at_gmail.com> wrote:
> Select s.name, count(*) from t_car c, t_link l, t_state s
> Where s.name not like 't%' and s.stateid = l.stateid(+)
> And l.carid = c.carid(+) and c.month = 201008
> GROUP BY s.name
> order BY s.name;
>
> I would like to see the following output :
>
> Arizona  2
> California  0
> Delaware  0
> Florida  3

Short fix...

  select s.name,

         count(c.carid)

    from t_car c,
         t_link l,
         t_state s
   where s.name not like 't%'
     and l.stateid(+) = s.stateid
     and c.carid(+) = l.carid
     and c.month(+) = 201008

   group by s.name
   order by s.name;

> But the query lists rows only when count(*) > 0.

That's because c.month = 201008 negates the outer join. Also, your count(*) will count all rows, including nulls.

  select s.name,

         count(c.carid) ct
    from t_state s
    left join t_link l

            on l.stateid = s.stateid
    left join t_car c

            on c.carid = l.carid
           and c.month = 201008

   where s.name not like 't%' -- ??
   group by

         s.stateid,
         s.name
   order by
         lower(s.name);

--
Peter

Received on Sun Sep 12 2010 - 19:57:51 CDT