Re: Query question

From: ddf <oratune_at_msn.com>
Date: Tue, 30 Aug 2011 14:30:24 -0700 (PDT)
Message-ID: <45b5cfbf-457b-4a70-8b23-409ea65dcc29_at_d25g2000yqh.googlegroups.com>

On Aug 30, 1:03 pm, The Magnet <a..._at_unsu.com> wrote:
> On Aug 30, 1:56 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
>
> > The Magnet wrote:
> > > Hi,
>
> > > We have a massive query.  But there is a problem.  One of the
> > > subqueries does not return any rows, therefore the entire query
> > > returns no rows.  I need to make it is one of the subqueries has no
> > > results, then it just gets NULL or 0.  The query is below.   Can
> > > someone help?
>
> > > Thanks!
>
> > That is normally solved with an "outer join".  Check it out.
>
> I'm not sure how I would do this with an outer join, since all of the
> columns are basically subqueries.  If one of the subqueries returns no
> results, why does the entire query fail?

It fails because it's an inner join:

SQL> select w.val, x.val, y.val, z.val
  2 from

  3  (select id, val from a where id =13) w,
  4  (select id, val from b where id =13) x,
  5  (select id, val from c where id =13) y,
  6  (select id, val from d where id =13) z

  7 where x.id = y.id and y.id = z.id and z.id = w.id;

no rows selected

SQL>
SQL> select w.val, x.val, y.val, z.val
  2 from
  3 (select id, val from a where id =13) w right outer join (select id, val from b where id =13) x on (w.id = x.id)   4 right outer join (select id, val from c where id =13) y on (x.id = y.id)
  5 right outer join (select id, val from d where id =13) z on (z.id = y.id);

VAL VAL VAL VAL
-------- -------- -------- --------

         00000013 00000013 00000013

SQL> Make it an outer join of some sort and it should return results.

David Fitzjarrell Received on Tue Aug 30 2011 - 16:30:24 CDT