Left Outer Join question

From: <bbcrock_at_gmail.com>
Date: 16 Feb 2006 12:20:46 -0800
Message-ID: <1140121246.824203.170140@g47g2000cwa.googlegroups.com>

My query looks like:

select a.type_id, sum(cost) as summary
from a, b, c, d, e
where a.type_id = e.type_id

and a.id = b.id
and a.id = d.id
and d.status_id = c.status_id
and d.status_id in (1,4)
and a.type_id in (1,2,3,4)
and a.year = '2006'

group by a.type_id

(the numeric values are passed into the query)

I need to modify it to select all of the e.type_ids as an outer join.

This query works great:

select e.type_id, sum(cost) as summary
from e, a
where e.type_id = a.type_id (+)
group by e.type_id

but bringing back any of the other joins or FILTERS create an inner join type result. that's right, I add one of the filters to the above query and the outer join stops.

I am stuck in T-SQL and ISO 99 and can't write this as pl-sql. Can anyone help?

I'm running Oracle 9i on windows.

thanks,

Don Received on Thu Feb 16 2006 - 14:20:46 CST