multiple outer joins problem

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)

   101, (AZ)
   102, (AZ)
   103, (AZ)
   111, (CA)
   131, (FL)
   132, (FL)
   133, (FL)

   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. Received on Fri Sep 10 2010 - 07:22:02 CDT