Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Left Outer Join question

Re: Left Outer Join question

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 17 Feb 2006 08:01:11 -0800
Message-ID: <1140192062.551382@jetspin.drizzle.com>


bbcrock_at_gmail.com wrote:
> 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

If you have 9i then just write the ANSI syntax. Oracle supports it.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Fri Feb 17 2006 - 10:01:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US