Re: multiple outer joins problem

From: ddf <oratune_at_msn.com>
Date: Fri, 10 Sep 2010 07:29:46 -0700 (PDT)
Message-ID: <052de12f-8f00-44b6-83a7-bf2bfe8f4c29_at_n19g2000prf.googlegroups.com>

On Sep 10, 8:22 am, "dn.p..._at_gmail.com" <dn.p..._at_gmail.com> wrote:
> I am trying to get a query right which uses multiple outer joins, bu
> something somewhere is wrong.
>
> There are three tables in the database : t_car, t_link, t_state
> The table t_car keeps track of which car was issued license number in
> which month. The table t_link keeps track of which license number was
> issued by which state. I am using multiple outer joins which do not
> work in the following query.
>
> Create table t_car(carid integer, month integer) ;
> Insert into t_car values (101, 201007) ;
> Insert into t_car values (102, 201008) ;
> Insert into t_car values (103, 201008) ;
> Insert into t_car values (111, 201007) ;
> Insert into t_car values (131, 201008) ;
> Insert into t_car values (132, 201008) ;
> Insert into t_car values (133, 201008) ;
>
> Create table t_link(carid integer, stateid varchar2(2)) ;
> Insert into t_link values (101, 'AZ') ;
> Insert into t_link values (102, 'AZ') ;
> Insert into t_link values (103, 'AZ') ;
> Insert into t_link values (111, 'CA') ;
> Insert into t_link values (131, 'FL') ;
> Insert into t_link values (132, 'FL') ;
> Insert into t_link values (133, 'FL') ;
>
> Create table t_state( stateid varchar2(2), name varchar2(32) ) ;
> Insert into t_state values ('AZ', 'Arizona') ;
> Insert into t_state values ('CA', 'California') ;
> Insert into t_state values ('DE', 'Delaware') ;
> Insert into t_state values ('FL', 'Florida') ;
>
> t_car (carid, month) , t_link (carid, stateid) , t_state (stateid,
> name)
> t_car :
>    101, 201007   (AZ)
>    102, 201008   (AZ, 201008 = 2010-August)
>    103, 201008   (AZ)
>    111, 201007   (CA)
>    131, 201008   (FL)
>    132, 201008   (FL)
>    133, 201008   (FL)
> t_link :
>    101, (AZ)
>    102, (AZ)
>    103, (AZ)
>    111, (CA)
>    131, (FL)
>    132, (FL)
>    133, (FL)
> t_state :
>    AZ  Arizona (one car in July, two in August)
>    CA  California (one car in July, none in August)
>    DE  Delaware (no cars)
>    FL  Florida (3 cars in August)
>
> 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
>
> But the query lists rows only when count(*) > 0 .
> Please advise. TIA.

SQL> select st.name,nvl(x.ct, 0) CT
  2 from t_state st left outer join
  3 (Select s.name, count(*) ct
  4 from t_car c, t_link l, t_state s
  5 Where s.name not like 't%'

  6  and s.stateid = l.stateid(+)
  7  And l.carid = c.carid(+)
  8  and c.month = 201008

  9 GROUP BY s.name ) x on (st.name = x.name)  10 order BY st.name;

NAME                                     CT
-------------------------------- ----------
Arizona                                   2
California                                0
Delaware                                  0
Florida                                   3

SQL> David Fitzjarrell Received on Fri Sep 10 2010 - 09:29:46 CDT