Re: Outer Join to a subquery alternative?

From: Ed Prochak <ed.prochak_at_magicinterface.com>
Date: Mon, 09 Feb 2004 00:52:37 -0500
Message-ID: <eSEVb.2877$ch2.19@fe01.usenetserver.com>

John wrote:

> Ed Prochak <ed.prochak_at_magicinterface.com> wrote in
> news:1pqUb.233$hJ6.115_at_fe01.usenetserver.com:
>
>

>>John wrote:
>>
>>
>>>Oracle doesnt like
>>>
>>>select whatever
>>>from maintable m,
>>>childtable c
>>>where c.seq(+)=(select max(seq) from childtable c where
>>>maintableseq=m.seq) 
>>
>>Your hint is in your question, to do an OUTER JOIN, you need to do a
>>JOIN first. IOW, get rid of the subquery.
>>
>>
>>If that not enough of a hint, here's a second one. Don't peek unless
>>you really need it.
>>ready?
>>look up the phrase "in-line view"

>
>
> Was a good reason why I didnt want to do it like this: the where clause in
> the main query is quite lengthy, and I dont want to duplicate it within a
> subquery. This would have a knockon effect to performance, whereas a
> subquery in the where clause in this way would implicitly only run for
> records in the main query that meet criteria.
>
> Cheers

Let me make sure I have it right.

Table maintable has columns:
seq (primary key??)
and others...

Table childtable has columns
seq (again primary key??)
maintableseq (a foreign key??)
and others...

The subquery is equivalent to
SELECT MAX(seq),maintableseq
  FROM childtable GROUP BY maintableseq ;

Which I suggested you do in-line, like this

select whatever
from maintable m,
childtable c,
( SELECT MAX(seq) as mcseq, maintableseq

     FROM childtable GROUP BY maintableseq ) mc where m.seq=mc.maintableseq
  and c.seq=mc.mcseq

Now, that covers everything EXCEPT the outer join rows and the rest of the WHERE clause in the original main query. Assuming the undisclosed WHERE conditions are all involved with the maintable Table, I'll ignore them for a moment.

Implied by the original query are two facts: there can be maintable rows without childtable rows (outer join) there can be many childtable rows for a maintable row

We want to get all of this in one query, as short and simple as possible, and we can do an outer join on only one table. But we has the child data in two tables. So let's merge them into another view.

This query gives all the children records which have the MAX(seq) select c.*
from childtable c,
( SELECT MAX(seq) as mcseq, maintableseq

     FROM childtable GROUP BY maintableseq ) mc WHERE mc.mcseq=c.seq ;

Now join that back to the maintable Table: select whatever
FROM maintable m,
( select c.*

   from childtable c,

       ( SELECT MAX(seq) as mcseq,  maintableseq
         FROM childtable GROUP BY maintableseq ) mc

   WHERE mc.mcseq=c.seq ) c2
WHERE m.seq=c.maintableseq(+)
<and other original query conditions> ;

Why won't something like that work for you? It has the MAX() condition, AND the outer join.

(Sometimes you just have to take the next step.)

HTH

-- 
Ed Prochak
running    http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Received on Sun Feb 08 2004 - 23:52:37 CST