Re: Query question

From: The Magnet <art_at_unsu.com>
Date: Fri, 2 Sep 2011 08:38:41 -0700 (PDT)
Message-ID: <ab9fb407-480d-4991-af9f-884211d8eedf_at_g9g2000yqb.googlegroups.com>

On Aug 30, 4:30 pm, ddf <orat..._at_msn.com> wrote:
> 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

Thanks Dave.

I got so frustrated that I broke the query into individual queries, stored the results in an object table and queried from that table. A hell of a lot more work. But, I'll try your solution too. Received on Fri Sep 02 2011 - 10:38:41 CDT