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.

On Sep 10, 8:22*am, "dn.p... (AT) gmail (DOT) com" <dn.p... (AT) gmail (DOT) com> wrote:
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

dn.perl (AT) gmail (DOT) com wrote:
David beat me to it, of course, since he's got nothing better to do.

Your syntax didn't provide the correct result, because the "c.month = 201008" defeats the outer
join (when the c record is null, c.month is not equal to 201008, nothing is equal to null).

The old oracle outer join syntax does allow to obtain the correct result though:

Select s.name, count(*),
sum(decode(c.month,201008,1,0))
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(+)
GROUP BY s.name
order BY s.name;


NAME COUNT(*) SUM(DECODE(C.MONTH,201008,1,0))
-------------------------------- ---------- -------------------------------
Arizona 3 2
California 1 0
Delaware 1 0
Florida 3 3

"dn.p... (AT) gmail (DOT) com" <dn.p... (AT) gmail (DOT) com> wrote:
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;

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